Category Archives: MySQL

How to Export MySQL data to CSV

MySQL command to export data in CSV format: SELECT id,product,quantity FROM orders WHERE foo = ‘bar’ INTO OUTFILE ‘/var/lib/mysql-csv/orders.csv’ FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘"’ LINES TERMINATED BY ‘\n’;SELECT id,product,quantity FROM orders WHERE foo = ‘bar’ INTO OUTFILE ‘/var/lib/mysql-csv/orders.csv’ FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘"’ LINES TERMINATED BY ‘\n’; Use this command from […]

Move MySQL Database with /etc/mysql/debian.cnf

When you move MySQL databases from datadir /var/lib/mysql/ to another server, remember to also copy the file /etc/mysql/debian.cnf to the new server, it contains the password used to connect to MySQL server via command-line. Very important if you use automated backups. Just a quick tutorial to move a MySQL database: 1) Stop MySQL server: /etc/init.d/mysql […]

Restore MySQL datadir on a new server

You can restore a previously copied\moved MySQL datadir on a new server: 1) Stop the MySQL service service mysql stopservice mysql stop 2) Rename the actual MySQL datadir to something different: mv /var/lib/mysql /var/lib/mysql_originalmv /var/lib/mysql /var/lib/mysql_original 3) Copy the MySQL datadir to restore in the MySQL datadir path: cp -R /backups/mysql /var/libcp -R /backups/mysql /var/lib […]

Backup MySQL datadir without Mysqldump

The fastest way to backup MySQL datadir is: 1) Stop the MySQL service service mysql stopservice mysql stop 2) Copy /var/lib/mysql to another location, i.e: cp -R /var/lib/mysql /root/mysqldatadircp -R /var/lib/mysql /root/mysqldatadir 3) Start the MySQL service service mysql startservice mysql start 4) Compress the new MySQL datadir with tar: tar czf /root/mysqldatadir.tar.gz /root/mysqldatadirtar czf […]

MySQL Find Duplicate Values Records

Fastest ways to find duplicate records in MySQL. If name is the column you want to find duplicates in: SELECT col1 FROM table GROUP BY col1 HAVING count(*) > 1;SELECT col1 FROM table GROUP BY col1 HAVING count(*) > 1; Is also possible to support multiple columns, like this: SELECT col1, col2, COUNT(*) c FROM […]

MySQL Remove Duplicates (Dupes)

Remove MySQL duplicates in place, without making a new table: ALTER IGNORE TABLE foobar ADD UNIQUE (email);ALTER IGNORE TABLE foobar ADD UNIQUE (email); *** This will keep the oldest duplicate record and erase the newer ones *** Works if index fits in memory and if MySQL < 5.7.4 *** Works fine with MariaDB 10.1 If […]

View all foreign keys to a table or column with MySQL

View all foreign keys to a table: SELECT TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_SCHEMA = ‘dbname’ AND REFERENCED_TABLE_NAME = ‘tablename’;SELECT TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_SCHEMA = ‘dbname’ AND REFERENCED_TABLE_NAME = ‘tablename’; View all foreign keys to a column: SELECT TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_SCHEMA = ‘dbname’ AND REFERENCED_COLUMN_NAME = ‘columnname’;SELECT TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME FROM […]

ERROR 1114 (HY000): The table ‘site’ is fullage done

If you get this MySQL error while you alter a table: ERROR 1114 (HY000): The table ‘site’ is fullage doneERROR 1114 (HY000): The table ‘site’ is fullage done Make sure the partition is not full. Type this command to see the partition status: df -hdf -h

Bash Autologin with MySQL MariaDB server

With MariaDB there is a file that contains user\pass for MySQL autologin: /etc/mysql/debian.cnf/etc/mysql/debian.cnf Here is an example output of /etc/mysql/debian.cnf: # Automatically generated for Debian scripts. DO NOT TOUCH! [client] host = localhost user = debian-sys-maint password = randomstring socket = /var/run/mysqld/mysqld.sock [mysql_upgrade] host = localhost user = debian-sys-maint password = randomstring socket = /var/run/mysqld/mysqld.sock […]

Disable Reverse DNS Lookup in MySQL

Edit /etc/mysql/my.cnf as follow: [mysqld] skip-name-resolve[mysqld] skip-name-resolve Then restart mysql service: /etc/init.d/mysql restart/etc/init.d/mysql restart