Category Archives: MySQL

Fix MariaDB 10.5 “Can’t create test file” errors

I found some issues on recent MariaDB 10.5 installation. The problems seem related to the new protections applied by MariaDB. Here is how I fixed them: 1) Could not increase number of max_open_files sed -i ‘s/LimitNOFILE=16384/LimitNOFILE=150000/’ /etc/systemd/system/mysqld.service sed -i ‘s/LimitNOFILE=16384/LimitNOFILE=150000/’ /lib/systemd/system/mariadb.service systemctl daemon-reloadsed -i ‘s/LimitNOFILE=16384/LimitNOFILE=150000/’ /etc/systemd/system/mysqld.service sed -i ‘s/LimitNOFILE=16384/LimitNOFILE=150000/’ /lib/systemd/system/mariadb.service systemctl daemon-reload 2) Can’t create […]

Use MariaDB and MySQL without Password (MySQL Backup)

On older MySQL versions you could use: /etc/mysql/debian.cnf/etc/mysql/debian.cnf To get MySQL username and password to use for backup purposes. On newer MySQL v8+ versions you just need to run this command as root user: mysql -u root -h localhost …mysql -u root -h localhost … Password is not required in this case. This is because […]

Bash Script to Dump a MySQL Table into a CSV File

Simple bash script to dump a MySQL table into a CSV file: mysql dbname -u’user’ -p’password’ <<QUERY_INPUT SELECT field1, field2, field3, field4 FROM table_name INTO OUTFILE ‘/path/file.csv’ FIELDS ENCLOSED BY ‘"’ TERMINATED BY ‘,’ ESCAPED BY ‘"’ LINES TERMINATED BY ‘\r\n’; QUERY_INPUTmysql dbname -u’user’ -p’password’ <<QUERY_INPUT SELECT field1, field2, field3, field4 FROM table_name INTO OUTFILE […]

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 […]