56、本地数据库迁移到阿里云_迁移数据库至阿里云

现有需求，本地数据库迁移到阿里云上。

库名xy102

表 test01

test02

test01 test02

3条数据。
1
2
3
4
5
6
7
8
9

1、登录阿里云界面创建免费试用ECS实列。

阿里云登录页 (aliyun.com)](https://account.aliyun.com/login/login.htm?oauth_callback=https%3A%2F%2Fusercenter2.aliyun.com%2Fhome%2Fmyfreetier)
1

2、选择2核4G，centos7.6，两台机器，然后设置账户密码，以及查看公网ip。

3、xshell客户端新建连接，ip地址为ECS实例的公网地址。

4、

[root@mysql1 opt]# systemctl stop firewalld
[root@mysql1 opt]# setenforce 0

[root@mysql1 opt]# systemctl restart mysqld

[root@aliyun1 ~]# systemctl stop firewalld
[root@aliyun1 ~]# setenforce 0
1
2
3
4
5
6
7

5、通过scp用mysql进行传输数据压缩包

[root@mysql1 opt]# scp mysql-8.0.30-el7-x86_64.tar.gz root@8.149.247.173:/opt/
The authenticity of host '8.149.247.173 (8.149.247.173)' can't be established.
ECDSA key fingerprint is SHA256:sJmF545LYGbyN6MrAoU/CpP9Wa0wrFxcnDlQ4h5RJvo.
ECDSA key fingerprint is MD5:c8:7d:d7:dd:54:4e:e0:a4:1b:7a:0b:f0:eb:7e:fa:c0.
Are you sure you want to continue connecting (yes/no)? yes

root@8.149.247.173's password: 
mysql-8.0.30-el7-x86_64.tar.gz     100%  470MB   4.1MB/s   01:54   
1
2
3
4
5
6
7
8

6、创建数据库xy102，以及test1、test2表

create database xy102;

CREATE TABLE test01(
id int(3),
name char(5),
address varchar(10)
);

INSERT into test01 values(1,'ly1','nanjing');
INSERT into test01 values(2,'ly2','nanjing');
INSERT into test01 values(3,'ly3','nanjing');

CREATE TABLE test02(
id int(3),
name char(5),
address varchar(10)
);
INSERT into test02 values(1,'ly1','chuzhou');
INSERT into test02 values(2,'ly2','chuzhou');
INSERT into test02 values(3,'ly3','chuzhou');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

7、备份数据库xy102.sql到/opt目录下

mysqldump -u root -p --databases xy102 > /opt/xy102.sql

8、阿里云主机安装mysql数据库

[root@aliyun1 /]# cd /opt/
[root@aliyun1 opt]# ls
mysql-8.0.30-el7-x86_64.tar.gz

tar -xf mysql-8.0.30-el7-x86_64.tar.gz 

 mv mysql-8.0.30-el7-x86_64 mysql

mv mysql /usr/local/

#创建程序用户管理
useradd -s /sbin/nologin mysql

#修改mysql目录和配置文件的权限
 chown -R mysql:mysql /usr/local/mysql/
[root@mysql1 mysql]# chown mysql:mysql /etc/my.cnf

#修改配置文件
 vim /etc/my.cnf
[client]
port = 3306
socket=/usr/local/mysql/mysql.sock

[mysqld]
user = mysql
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
port = 3306
character-set-server=utf8
pid-file = /usr/local/mysql/mysqld.pid
socket=/usr/local/mysql/mysql.sock
bind-address = 0.0.0.0
skip-name-resolve
max_connections=2048
default-storage-engine=INNODB
max_allowed_packet=16M
server-id = 1
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION



#设置环境变量，申明/宣告mysql命令便于系统识别
 echo "PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile
 source /etc/profile

#初始化数据库：
 cd /usr/local/mysql/bin/
cd /usr/local/mysql/bin/
./mysqld \
--initialize-insecure \
--user=mysql \
--basedir=/usr/local/mysql \
--datadir=/usr/local/mysql/data

报错：

./mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory

解决办法：

原因分析：

服务器上面没有安装libiao这个包，需要我们去下载一下就好了

yum -y install libaio

./mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

#设置系统识别，进行操作：
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
chmod +x /etc/init.d/mysqld
 systemctl daemon-reload
ystemctl restart mysqld

#初始化数据库密码：
mysqladmin -u root -p password "123456"
直接回车即可

#进入数据库：
mysql -u root -p123456
#创建用户并设置密码：
mysql> CREATE USER 'root'@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.01 sec)

#赋予远程连接的权限
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';
Query OK, 0 rows affected (0.00 sec)

#刷新生效
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

#修改加密方式，可以进行远程连接
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94

9、传送xy102数据库到阿里云主机上

scp xy102.sql root@8.149.247.173:/opt/
root@8.149.247.173's password: 
xy102.sql                          100% 2769    97.1KB/s   00:00 
1
2
3

10、热备份恢复数据库到阿里云主机上

cd /opt/
[root@aliyun1 opt]# ls
mysql-8.0.30-el7-x86_64.tar.gz  xy102.sql
[root@aliyun1 opt]# mysql -u root -p < /opt/xy102.sql
Enter password: 
[root@aliyun1 opt]# mysql -u root -p123456
use xy102;
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_xy102 |
+-----------------+
| test01          |
| test02          |
+-----------------+
2 rows in set (0.01 sec)

mysql> select *from  test01;
+------+------+---------+
| id   | name | address |
+------+------+---------+
|    1 | ly1  | nanjing |
|    2 | ly2  | nanjing |
|    3 | ly3  | nanjing |
+------+------+---------+
3 rows in set (0.00 sec)

mysql> select * from  test02;
+------+------+---------+
| id   | name | address |
+------+------+---------+
|    1 | ly1  | chuzhou |
|    2 | ly2  | chuzhou |
|    3 | ly3  | chuzhou |
+------+------+---------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

解决navicat连接问题