Install LAMP Stack on Ubuntu
It’s very easy since Ubuntu 10.04: $> sudo apt-get update $> sudo apt-get install lamp-server^ The ^ is no typing error. Its necessary! Further information:Â https://help.ubuntu.com/community/ApacheMySQLPHP
Category: MySQL
Use column alias in conditions
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 […]
Show queries in live time
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).
Check and optimize tables in MySQL
To check and optimize tables of a MySQL database: $> mysqlcheck -os <db_name> -u<username> -p To achieve the same for all databases of the respective MySQL server you can run the following command: $> mysqlcheck -Aos -u<username> -p Further information about check and optimize tables in MySQL To get a few more facts about how […]
External access to MySQL database
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 […]
Substring or split
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.
Find out where my.cnf is located
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, […]
Copy Database directly to another Database
You can directly copy a database from one host to another host like this: $> mysqldump -u SOURCE_DB_user –password=SOURCE_DB_pass -h SOURCE_DB_HOST SOURCE_DB_name | mysql -u DB_user –password=DB_pass -h DB_host DB_name
Import database
$> mysql -h server.host.de -u xxxxxxx -pxxxxxxx database < dump.sql or better: $> gunzip < backup-20120514151839.sql.gz | mysql -u xxxxx -pxxxxx database
Export database
To compromise (gzip) the output: Usefull Params –single-transaction –quick –opt Export al​l Databases Through an ssh tunnel