MySQL: Notes

Ways to check MySQL version

mysql --version
mysql -V
dpkg -l 'mysql-sever'
mysql -u root -p

How to check MySQL version: mysql --version

Database folder

On Microsoft Windows, by default, MySQL save databases in this folder:

%ProgramData%\MySQL\MySQL Server 5.5\data

Check databases' size

SELECT table_schema AS "Database name", SUM(data_length + index_length) / 1024 / 1024 AS "Size (MB)" FROM information_schema.TABLES GROUP BY table_schema;

Export database schema

$ mysqldump -u root -p --no-data wordpress1001 > c:\backup\schema-wordpress1001.sql
Enter password: ****************

Create a dedicated user to backup databases

Give credit to: http://www.fromdual.com/privileges-of-mysql-backup-user-for-mysqldump

Create dedicated mysql user and grant privileges to run backup

$ mysql -u root -p

CREATE USER 'backupdb'@'localhost' IDENTIFIED BY 'passwordhere';
GRANT SELECT,SHOW VIEW,RELOAD,REPLICATION CLIENT,EVENT,TRIGGER ON *.* TO 'backupdb'@'localhost';
GRANT LOCK TABLES ON *.* TO 'backupdb'@'localhost';
FLUSH PRIVILEGES;
QUIT;

Backup all databases

mysqldump -u backupdb -p --all-databases | gzip -c > /backup/databases-$(date '+%Y%m%d%H%M').sql.gz

Backup all databases using dedicated mysql user backupdb

Check databases' size

$ mysql -u root -p

SELECT table_schema AS "Database", 
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size(MB)" 
FROM information_schema.TABLES 
GROUP BY table_schema;

Change user's password

ALTER USER 'username'@'localhost' IDENTIFIED BY 'Enter-new-password';

MySQL/MariaDB Client

apt install mycli

MySQL Shell for VS Code