hackerzhou's wiki

Link to this comparison view

--- dev:kb:database:mysql [2014/02/01 11:46] – created hackerzhou
+++ dev:kb:database:mysql [2020/06/06 23:32] (current) – external edit 127.0.0.1
@@ Line 1: / Line 1: @@
 ====== MySQL ======
+==== Update MySQL root password ====
+<code bash>
+sudo mysqladmin -u root -p password '<new_password>'
+</code>
+==== Allow MySQL root remote access ====
+<code sql>
+update mysql.user set host = '%' where user = 'root';
+</code>
+==== Create backup account ====
+<code sql>
+create user backupuser@localhost identified by 'backuppassword';
+grant lock tables, select on *.* to backupuser@localhost identified by 'backuppassword';
+</code>
 ==== MySQL backup & restore SQL file ====
 <code bash>
-mysqldump -uusername -ppassword --all-databases > all.sql
+mysqldump -ubackupuser -pbackuppassword --all-databases --add-drop-database > all.sql
-mysql -uusername -ppassword < all.sql;
+mysql -ubackupuser -pbackuppassword < all.sql;
+</code>
+==== Create account and assign privileges on a database ====
+<code sql>
+create user <db_user>@localhost identified by '<db_password>';
+grant all privileges on <db_name>.* to <db_user>@localhost identified by '<db_password>';
+</code>
+==== Show all accounts ====
+<code sql>
+select user,host,password from mysql.user;
+</code>
+==== Create database using UTF-8 charset ====
+<code sql>
+CREATE database IF NOT EXISTS <db_name> default charset utf8 COLLATE utf8_general_ci;
 </code>