MySQL数据库同步部署

Maiabo

于 2024-05-07 10:53:39 发布

阅读量938

点赞数 9

文章标签：数据库 mysql

本文链接：https://blog.csdn.net/weixin_73725203/article/details/138523443

版权

1.实战部署主从复制（一主一从）

1>环境准备

角色	IP	版本
master	10.0.0.118	mysql5.7.44
slave	10.0.0.119	mysql5.7.44

2>安装数据库（主从都需要安装）

[root@master ~]# wget -i -c http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm
[root@master ~]# yum -y install mysql57-community-release-el7-10.noarch.rpm
[root@master ~]# rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022
[root@master ~]# yum -y install mysql-community-server
[root@master ~]# systemcctl restart mysqld

3>修改密码（主从都作）

先查看初始密码
[root@slave ~]#grep 'password' /var/log/mysqld.log
2024-01-30T06:45:04.580882Z 1 [Note] A temporary password is generated for root@localhost: 4eGo3m+ZpWXn
[root@slave ~]#mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.44

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

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> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)

mysql> set global validate_password_length=6;
Query OK, 0 rows affected (0.00 sec)

mysql> set password for 'root'@'localhost'=password('123456');
Query OK, 0 rows affected, 1 warning (0.00 sec)

4>配置master

a.配置同步数据库

mysql> create database HA;
Query OK, 1 row affected (0.00 sec)

mysql> use HA;
Database changed
mysql> create table t1 (id int,name varchar(20));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values (1,'zhangsan');
Query OK, 1 row affected (0.04 sec)

b.配置my.cnf文件

[root@master ~]#vim /etc/my.cnf
log-bin=mysql-bin-master
server-id=1
binlog-do-db=HA
binlog-ignore-db=mysql

c.重启服务

[root@master ~]#systemctl restart mysqld

d.授权连接用户

mysql> grant replication slave on *.* to slave@10.0.0.119 identified by '123456';
Query OK, 0 rows affected, 1 warning (0.01 sec)

如出现下面问题
mysql> grant replication slave on *.* to slave@10.0.0.119 identified by '123456';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
解决方法
mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)

mysql> set global validate_password_length=6;
Query OK, 0 rows affected (0.00 sec)

mysql> grant replication slave on *.* to slave@10.0.0.119 identified by '123456';
Query OK, 0 rows affected, 1 warning (0.01 sec)

e.查看服务器状态

mysql> show master status;
+-------------------------+----------+--------------+------------------+-------------------+
| File                    | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------------+----------+--------------+------------------+-------------------+
| mysql-bin-master.000002 |      409 | HA           | mysql            |                   |
+-------------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

f.查看binlog日志文件的所在位置

g.导出数据库并传给从服务器

[root@master ~]#mysqldump -uroot -p123456 HA >HA.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@master ~]#scp HA.sql root@10.0.0.119:/root

5>配置从数据库服务器

a.测试使用slave账号是否可以链接主服务器

[root@slave ~]#mysql -uslave -p123456 -h 10.0.0.118
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 4
Server version: 5.7.44-log MySQL Community Server (GPL)

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

mysql> exit
Bye

b.从服务器创建HA库，并导入数据

[root@slave ~]#mysql -uroot -p123456  
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 7
Server version: 5.7.44 MySQL Community Server (GPL)

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

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 HA;
Query OK, 1 row affected (0.00 sec)

mysql> exit

[root@slave ~]#mysql -uroot -p123456 HA<HA.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.

c.修改从服务器配置文件

[root@slave ~]#vim /etc/my.cnf
server-id = 2 #从服务器ID号，不要和主ID相同 ，如果设置多个从服务器，每个从服务器必须有一个唯
一的server-id值，必须与主服务器的以及其它从服务器的不相同。可以认为server-id值类似于IP地址：
这些ID值能唯一识别复制服务器群集中的每个服务器实例。
read_only=ON #注意：此限制对拥有SUPER权限的用户均无效

d.重启服务器

[root@slave ~]#systemctl restart mysqld

e.使用如下命令查看从机是否运行，如果运行则先停止

[root@slave ~]#mysql -uroot -p123456
mysql> show slave status\G
Empty set (0.00 sec)
mysql> stop slave; #先停止slave
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql>mysql> change master to
master_host='192.168.1.101',master_user='slave',master_password='123456',master_
port=3306,master_log_file='mysql-master-bin.000002',master_log_pos=450;
;##指定同步用户，master_log_file要与主服务器一致
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave; #开启slave同步
Query OK, 0 rows affected (0.01 sec)

f.查看状态

Slave_IO_Running ：一个负责与主机的io通信
Slave_SQL_Running：负责自己的slave mysql进程
如果以上两个进程都是"Yes",则说明同步完成。

g.到主机上查看状态

mysql> show processlist \G;
*************************** 1. row ***************************
     Id: 2
   User: root
   Host: localhost
     db: NULL
Command: Query
   Time: 0
  State: starting
   Info: show processlist
*************************** 2. row ***************************
     Id: 5
   User: slave
   Host: 10.0.0.119:34216
     db: NULL
Command: Binlog Dump
   Time: 44
  State: Master has sent all binlog to slave; waiting for more updates
   Info: NULL
2 rows in set (0.00 sec)

ERROR: 
No query specified

6>查看数据是否同步

a.在主机上尝试插入数据

mysql> show tables;
+--------------+
| Tables_in_HA |
+--------------+
| t1           |
+--------------+
1 row in set (0.00 sec)

mysql> select * from t1;
+------+----------+
| id   | name     |
+------+----------+
|    1 | zhangsan |
+------+----------+
1 row in set (0.00 sec)

mysql> insert into t1 values (2,'ll');
Query OK, 1 row affected (0.00 sec)

b.在从服务器查看

mysql> select * from t1;
+------+----------+
| id   | name     |
+------+----------+
|    1 | zhangsan |
|    2 | ll       |
+------+----------+
2 rows in set (0.00 sec)

2.部署主主双向主从复制

1>环境准备

角色	IP
master01	10.0.0.110
master02	10.0.0.118

2>安装mysql（两台都要）

[root@master01 ~]# wget -i -c http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm
[root@master01 ~]# yum -y install mysql57-community-release-el7-10.noarch.rpm
[root@master01 ~]# rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022
[root@master01 ~]# yum -y install mysql-community-server

3>修改密码

[root@master01 ~]#grep 'password' /var/log/mysqld.log
2024-01-30T06:45:04.580882Z 1 [Note] A temporary password is generated for root@localhost: 4eGo3m+ZpWXn
[root@master01 ~]#mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.44

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

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> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)

mysql> set global validate_password_length=6;
Query OK, 0 rows affected (0.00 sec)

mysql> set password for 'root'@'localhost'=password('123456');
Query OK, 0 rows affected, 1 warning (0.00 sec)

4>配置master01服务器

a.配置my.cnf

[root@master01 ~]# vim /etc/my.cnf
log-bin=mysql-bin-master #启用二进制日志
server-id=1 #本机数据库ID 标示
binlog-do-db=HA #可以被从服务器复制的库。二进制需要同步的数据库名
binlog-ignore-db=mysql #不可以被从服务器复制的库
validate-password=off #关闭密码审计强度

b.重启服务器

[root@master01 ~]#systemctl restart mysqld

c.查看mater01的状态

mysql> create database HA;
Query OK, 1 row affected (0.00 sec)

mysql> show master status;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    2
Current database: *** NONE ***

+-------------------------+----------+--------------+------------------+-------------------+
| File                    | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------------+----------+--------------+------------------+-------------------+
| mysql-bin-master.000001 |      154 | HA           | mysql            |                   |
+-------------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

d.授权可以同步本端数据库的用户

[root@master01 ~]#mysql -uroot -p
Enter password:
mysql> grant replication slave on *.* to slave@'10.0.0.118' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

5>配置master02

a.配置my.cnf

b.重启数据库

[root@master02 ~]#systemctl restart mysqld

c.测试一下从账号登录是否正常

[root@master02 ~]#mysql -uslave -p123456 -h 10.0.0.110
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 3
Server version: 5.7.44-log MySQL Community Server (GPL)

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

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>

d.授权master01同步账号

[root@master02 ~]#mysql -uroot -p123456
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 2
Server version: 5.7.44-log MySQL Community Server (GPL)

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

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> grant replication slave on *.* to slave@'10.0.0.110' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

e.指定主服务器是master01

[root@master02 ~]#mysql -uroot -p123456
mysql> change master to master_host='10.0.0.110',master_user='slave',master_password='123456';
Query OK, 0 rows affected, 2 warnings (0.01 sec)

f.开启同步

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

g.查看同步状态

6>配置master01

a.测试从账号是否能登陆

[root@master01 ~]#mysql -uslave -p123456 -h 10.0.0.118
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 5
Server version: 5.7.44-log MySQL Community Server (GPL)

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

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>

b.指定master02是主服务器

[root@master01 ~]#mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.44 MySQL Community Server (GPL)

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

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> change master to master_host='10.0.0.118',master_user='slave',master_password='123456';
Query OK, 0 rows affected, 2 warnings (0.02 sec)

c.开启同步

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

d.查看同步状态

7>测试

在master01数据库上插入数据，然后到master02上查看数据是否同步

mysql> use HA;
Database changed

mysql> create table demo(id int ,name varchar(20));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into demo values (1,'zhangsan');
Query OK, 1 row affected (0.01 sec)

mysql> insert into demo values (2,'lisi');
Query OK, 1 row affected (0.01 sec)

在master02先创建HA数据库后重启服务再查看

mysql> use HA;
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_HA |
+--------------+
| demo         |
+--------------+
1 rows in set (0.01 sec)

mysql> select * from demo;
+------+----------+
| id   | name     |
+------+----------+
|    1 | zhangsan |
|    2 | lisi     |
+------+----------+
2 rows in set (0.00 sec)

3.部署M-S-S模式（级联复制）

1>环境部署

角色	IP
master	10.0.0.110
中继	10.0.0.118
slave	10.0.0.119

2>安装mysql（三台都要）

[root@master ~]# wget -i -c http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm
[root@master ~]# yum -y install mysql57-community-release-el7-10.noarch.rpm
[root@master ~]# rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022
[root@master ~]# yum -y install mysql-community-server
[root@master ~]# systemcctl restart mysqld

3>修改密码

[root@slave ~]#grep 'password' /var/log/mysqld.log
2024-01-30T06:45:04.580882Z 1 [Note] A temporary password is generated for root@localhost: 4eGo3m+ZpWXn
[root@slave ~]#mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.44

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

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> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)

mysql> set global validate_password_length=6;
Query OK, 0 rows affected (0.00 sec)

mysql> set password for 'root'@'localhost'=password('123456');
Query OK, 0 rows affected, 1 warning (0.00 sec)

4>配置master

a.修改my.cnf配置文件

[root@master ~]#vim /etc/my.cnf
server-id=1
binlog-do-db=HA
log-bin=mysql-bin-master
binlog-ignore-db=mysql
sync-binlog=1
binlog-format=row

b.重启服务

[root@master ~]# systemctl restart mysqld

c.创建HA库，并添加数据

mysql> create database HA;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    2
Current database: *** NONE ***

Query OK, 1 row affected (0.01 sec)

mysql> use HA;
Database changed
mysql> create table demo(id int,name varchar(20));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into demo values (1,'zhangsan');
Query OK, 1 row affected (0.00 sec)

d.授权中继服务器可以连接master

mysql> grant replication slave on *.* to 'repl'@'10.0.0.118' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)

5>部署中继服务器

a.配置my.cnf配置文件

[root@relay ~]#vim /etc/my.cnf
server-id= 2 #修改主配置文件也要开启bin-log：
log-bin=mysql-bin-slave1
log-slave-updates=1 #把它从relay-log当中读取出来的二进制日志并且这本机上执行的操作也记录
这自己的二进制日志里面，这样才能使第三台slave通过中继slave读取到相应数据变化
binlog-format=row

b.重启服务器

[root@relay ~]#systemctl restart mysqld

c.授权让主服务器（10.0.0.110）成为中继服务器的master

mysql> change master to master_host='10.0.0.110',master_user='repl',master_password='123456';
Query OK, 0 rows affected, 2 warnings (0.01 sec)

d.授权一个用户给从服务器（10.0.0.119），用于拉取中继服务器的数据

mysql> grant replication slave on *.* to 'repl'@'10.0.0.119' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

6>配置从服务器

a.修改my.cnf配置文件

[root@slave ~]# vim /etc/my.cnf
server-id = 3
log-bin=mysql-bin-slave2
binlog-format=row
validate-password=off

b.重启服务

[root@slave ~]#systemctl restart mysqld

c.指定中继服务器（10.0.0.118）为主服务器

mysql> change master to master_host='10.0.0.118',master_user='repl',master_password='123456';
Query OK, 0 rows affected, 2 warnings (0.01 sec)

d.查看服务器状态

7>测试

a.重启中继服务器，然后查看HA数据库是否存在

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| HA                 |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use HA;
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_HA |
+--------------+
| demo         |
+--------------+
1 row in set (0.00 sec)

b.重启从服务器，然后查看HA数据库是否存在

mysql> use HA;
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_HA |
+--------------+
| demo         |
+--------------+
1 row in set (0.00 sec)