Differences

This shows you the differences between two versions of the page.

--- dev:kb:database:mysql [2014/02/01 11:52] – [Create backup account] 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>
@@ Line 7: / Line 16: @@
 ==== MySQL backup & restore SQL file ====
 <code bash>
-mysqldump -ubackupuser -pbackuppassword --all-databases > all.sql
+mysqldump -ubackupuser -pbackuppassword --all-databases --add-drop-database > all.sql
 mysql -ubackupuser -pbackuppassword < all.sql;
 </code>
@@ Line 15: / Line 24: @@
 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>