Category Archives: MySQL

How to Create Backups of MySQL via PHP

If you website is hosted on a web hosting like OVH, you should make sure to periodically backup your MySQL database as additional precaution, just in case the automated backup offered by the hosting provider fails. With OVH, for example, you can create cronjobs that execute PHP files located in your FTP home directory, here […]

Disable MySQL Binary Logging (Turn Off Log_Bin Variable)

You can disable MySQL binary logging by adding this line to /etc/mysql/my.cnf: skip-log-binskip-log-bin Make sure to add it below [mysqld] section. Then restart MySQL service like this: /etc/init.d/mysql restart/etc/init.d/mysql restart To verify binary loggin is disabled, login to MySQL as root and type: SHOW VARIABLES LIKE "log_bin";SHOW VARIABLES LIKE "log_bin"; The result should be like […]

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