mysql迁移之新建用户、备份还原数据库

最近要把之前的一个项目迁移到云上，涉及到数据库迁移，然后发现太久没搞很多都忘了，就变搞边整理一下吧

导出数据

备份数据

操作命令：

  mysqldump -uUSERNAME -pPASSWORD DBNAME > FILENAME.sql

具体事例：

  zixie-vps# mysqldump -uzixie -p'zixie' zixieDB > zixie_back.sql
  mysqldump: [Warning] Using a password on the command line interface can be inse.
  zixie-vps# ll
  total 5452
  -rw-r--r-- 1 root root 5580687 Jan 23 12:53 zixie_back.sql.sql

下载备份

操作命令：

  scp -P PORT -r USERNAME@HOST:PATH LOCALPATH

具体事例：

  ➜  1  scp -r root@127.0.0.1:/temp/zixie_back.sql ./

新建用户

操作步骤

root账号登录mysql

  ➜  1  mysql -uroot -p
  Enter password:
  Welcome to the MySQL monitor.  Commands end with ; or \g.
  Your MySQL connection id is 8
  Server version: 8.0.13 Homebrew
	
  Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
	
  Oracle is a registered trademark of Oracle Corporation and/or its
  affiliates. Other names may be trademarks of their respective
  owners.
	
  Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
	
  mysql>

新增用户

命令:

  CREATE USER 'USERNAME'@'HOST' IDENTIFIED BY 'PASSWORD';

说明：HOST指定该用户在哪个主机上可以登陆，如果是本地用户用localhost，如果可以从任意远程主机登陆，可以使用通配符%

事例

  mysql> CREATE USER 'zixie'@'%' IDENTIFIED BY 'zixie';
  Query OK, 0 rows affected (0.10 sec)
		
  mysql> select User from mysql.user where User='zixie';
  +-------+
  | User  |
  +-------+
  | zixie |
  +-------+
  1 row in set (0.00 sec)

  mysql>

授权访问
- 命令:
```
  GRANT PRIVILEGES ON DBNAME.TABLENAME TO 'USERNAME'@'HOST' WITH GRANT OPTION;
```
- 说明:
  - PRIVILEGES是用户的操作权限，如SELECT，INSERT，UPDATE等，如果要授予所的权限则使用ALL
  - DBNAME.TABLENAME是要授权的数据库和表，如果给不限制用*表示
  - WITH GRANT OPTION表示该用户还阔以给其余用户授权
- 事例
```
  mysql> GRANT ALL ON *.* TO 'zixie'@'%' WITH GRANT OPTION;
  Query OK, 0 rows affected (0.12 sec)
		
  mysql> flush privileges;
  Query OK, 0 rows affected (0.01 sec)
		
  mysql>
```

异常情况

使用root用户登录，提示 Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’

事例：

  ➜  1  mysql -uroot -p""
  Enter password:
  ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

原因：你的mysql还没有启动，阔以使用mysql.server start启动

解决方案：

  ➜  1  mysql.server start
  Starting MySQL
  .. SUCCESS!
  ➜  1  mysql -uroot -p
  Enter password:
  Welcome to the MySQL monitor.  Commands end with ; or \g.
  Your MySQL connection id is 8
  Server version: 8.0.13 Homebrew
		
  Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
		
  Oracle is a registered trademark of Oracle Corporation and/or its
  affiliates. Other names may be trademarks of their respective
  owners.
		
  Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
		
  mysql>

忘记root密码，可以参照文档：Linux 下修改mysql的root密码

新建DB，导入数据

登录新的数据库，并新建DB，设置字符集

  ➜  1  mysql -uzixie -p"zixie"
  mysql: [Warning] Using a password on the command line interface can be insecure.
  Welcome to the MySQL monitor.  Commands end with ; or \g.
  Your MySQL connection id is 28
  Server version: 8.0.13 Homebrew
	
  Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
	
  Oracle is a registered trademark of Oracle Corporation and/or its
  affiliates. Other names may be trademarks of their respective
  owners.
	
  Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
	
  mysql> create database game;
  Query OK, 1 row affected (0.09 sec)
	
  mysql> use game;
  Database changed
  mysql> set names utf8;
  Query OK, 0 rows affected, 1 warning (0.00 sec)
	
  mysql> exit;

导入数据

命令:

  mysql -uUSERNAME -pPASSWORD DBNAME < FILENAME.sql

事例

  ➜  1  mysql -uzixie -p'zixie' game < /temp/zixie_back.sql
  mysql: [Warning] Using a password on the command line interface can be insecure.
  ➜  1  mysql -uzixie -p"zixie"
  mysql: [Warning] Using a password on the command line interface can be insecure.
  Welcome to the MySQL monitor.  Commands end with ; or \g.
  Your MySQL connection id is 30
  Server version: 8.0.13 Homebrew
		
  Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
		
  Oracle is a registered trademark of Oracle Corporation and/or its
  affiliates. Other names may be trademarks of their respective
  owners.
		
  Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
		
  mysql> use game;
  Reading table information for completion of table and column names
  You can turn off this feature to get a quicker startup with -A
		
  Database changed
  mysql> show tables;
  +----------------+
  | Tables_in_game |
  +----------------+
  | t_admin        |
  | t_adventure    |
  | t_advice       |
  | t_game         |
  | t_player       |
  | t_statistic    |
  | t_tips         |
  | t_userinput    |
  | t_vampire      |
  | t_vampirenum   |
  | t_vampiretimes |
  | t_vampirewords |
  +----------------+
  12 rows in set (0.00 sec)
		
  mysql>	

那时年少

微信公共平台

前端开发

开发资源

终端开发

开发工具

经验总结

玩一玩

在路上

后台开发

标签

总结 8

公共平台 3

wordpress 1

API 1

web 9

资源 4

android 50

log 1

adb 6

学习资源 1

md5 1

bug 7

svn 2

命令 3

常用工具 8

Markdown 3

markdown 2

工具 30

经验 3

SDK 22

Android 13

NDK 1

jni 1

个人作品 7

终端 1

测试 7

签名 1

iMac 13

调试 1

学习 1

gradle 7

ant 1

旅行 8

php 1

git 2

mac 2

java 1

androir 1

sqlite 1

mysql 1

tags 53

apk 5

图片 1

规划 1

chrome 1

iOS 1

jekyll 1

ruby 1

schema 1

自动 3

appium 2

linux 1

webview 1

开发资源 1