To get information about the currently connected user and more MySQL status information, you can call the following command if you are already logged in: This will give you an output like that: ————– mysql Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1 Connection id: 1044395 Current database: Current user: niceguy@localhost SSL: Not in […]
To crate a UTF-8 database: CREATE DATABASE sns_typo3 DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;
With the use of HAVING you can use a result columns of a database query in itself: SELECT category_field = 3 AS ‘result’ FROM catalog_category_entity WHERE condition_field = 985 HAVING result = 1; Here the alias result (see AS ‘result’) for the only column is used as a condition with HAVING. That is by now […]
You can see all running queries in live time with: $> mysqladmin -u dbuser -p123 -i 2 processlist The option -i determines the number of seconds between refreshes(alias –sleep=DELAY).
Plesk changes the root user name to admin and stores the corresponding password in /etc/psa/.psa.shadow. The value inside this file looks really strange – like an encrypted key or hash. But that’s really your password for the admin user! If you’re lazy – like me – you can login as admin like that: $> mysql -uadmin […]
To gain external access to MySQL database (f.e. in PHPSTorm or an external PHPMyAdmin) you can do: GRANT necessary privileges: CREATE USER ‘wdn_live_user’@’%’ IDENTIFIED BY ‘***’; GRANT USAGE ON *.* TO ‘wdn_live_user’@’%’ IDENTIFIED BY ‘***’ WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0; GRANT ALL PRIVILEGES ON `wdn_live`.* TO ‘wdn_live_user’@’%’; If the user already […]
MySQL don’t now an explode or split function. But you can use SUBSTRING_INDEX: SUBSTRING_INDEX(caet_ast.value, “\n”, 1) as street_name, SUBSTRING_INDEX(caet_ast.value, “\n”, -1) as street_nbr, This gets Testweg for street_name and 881 for street_nbr if caet_ast.value was “Testweg\n 881”. The function is also nestable: SUBSTRING_INDEX(SUBSTRING_INDEX(path, ‘/’, 2), ‘/’, -1) This makes 3 if path was 1/3/342/357/363.
To find out where the MySQL configuration (my.cnf) is stored, you can use the strace command: $> strace mysql “;” 2>&1 | grep my.cnf You will get an output showing all the paths the MySQL deamon searches for the my.cnf upon startup like that: stat(“/etc/my.cnf”, 0x7fffecb75210) = -1 ENOENT (No such file or directory) stat(“/etc/mysql/my.cnf”, {st_mode=S_IFREG|0644, st_size=3505, […]
$> mysql -h server.host.de -u xxxxxxx -pxxxxxxx database < dump.sql or better: $> gunzip < backup-20120514151839.sql.gz | mysql -u xxxxx -pxxxxx database
To compromise (gzip) the output: Usefull Params –single-transaction –quick –opt Export al​l Databases Through an ssh tunnel
