User Tools

Site Tools


dev:kb:database:mysql

MySQL

Update MySQL root password

sudo mysqladmin -u root -p password '<new_password>'

Allow MySQL root remote access

UPDATE mysql.user SET host = '%' WHERE USER = 'root';

Create backup account

CREATE USER backupuser@localhost IDENTIFIED BY 'backuppassword';
GRANT LOCK TABLES, SELECT ON *.* TO backupuser@localhost IDENTIFIED BY 'backuppassword';

MySQL backup & restore SQL file

mysqldump -ubackupuser -pbackuppassword --all-databases --add-drop-database > all.sql
mysql -ubackupuser -pbackuppassword < all.sql;

Create account and assign privileges on a database

CREATE USER <db_user>@localhost IDENTIFIED BY '<db_password>';
GRANT ALL privileges ON <db_name>.* TO <db_user>@localhost IDENTIFIED BY '<db_password>';

Show all accounts

SELECT USER,host,password FROM mysql.user;

Create database using UTF-8 charset

CREATE DATABASE IF NOT EXISTS <db_name> DEFAULT charset utf8 COLLATE utf8_general_ci;
dev/kb/database/mysql.txt · Last modified: 2020/06/06 23:32 by 127.0.0.1