Linux学习之MySQL主从复制

MySQL配置一主一从

环境准备：
两台服务器： Master：192.168.88.53，Slave：192.168.88.54
在两台服务器上安装mysql-server

# 配置主服务器192.168.88.53
# 启用binlog日志
[root@mysql53 ~]# yum -y  install mysql-server  mysql
[root@mysql53 ~]# vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server-id=53
log-bin=mysql53
[root@mysql53 ~]# systemctl  start mysqld
# 用户授权
[root@mysql53 ~]# mysql
mysql> create user repluser@"%" identified by "123456";
Query OK, 0 rows affected (0.11 sec)
mysql> grant replication slave on *.*  to repluser@"%";
Query OK, 0 rows affected (0.09 sec)
# 查看日志信息
mysql> show master status;
+----------------+----------+--------------+------------------+-------------------+
| File           | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------+----------+--------------+------------------+-------------------+
| mysql53.000002 |      667 |              |                  |                   |
+----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

将Slave192.168.88.54配置为从数据库服务

# 指定server-id并启动数据库服务
[root@mysql54 ~]# yum -y  install mysql-server  mysql
[root@mysql54 ~]# vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server-id=54
[root@mysql54 ~]# systemctl  start mysqld
# 登陆服务指定主服务器信息
mysql> change master to master_host="192.168.88.53",master_user="repluser",master_password="123456",master_log_file="mysql53.000002",master_log_pos=667;
Query OK, 0 rows affected, 8 warnings (0.85 sec)
# 启动slave 进程
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.10 sec)
# 查看信息状态
mysql> show slave status\G
*************************** 1. row ***************************Slave_IO_State: Waiting for source to send eventMaster_Host: 192.168.88.53Master_User: repluserMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql53.000002Read_Master_Log_Pos: 667Relay_Log_File: mysql54-relay-bin.000002Relay_Log_Pos: 322Relay_Master_Log_File: mysql53.000002Slave_IO_Running: Yes           # IO线程（YES表示正常）Slave_SQL_Running: Yes           # SQL线程（YES表示正常）Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 667Relay_Log_Space: 533Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 53Master_UUID: bb19b901-52ca-11ee-86a6-525400605619Master_Info_File: mysql.slave_master_infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Replica has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0Network_Namespace: 
1 row in set, 1 warning (0.00 sec)
# 在主服务器添加用户，给客户端连接使用
mysql> create user plj@'%' identified by "123456";
Query OK, 0 rows affected (0.10 sec)
mysql> grant all privileges on gamedb.* to plj@'%';
Query OK, 0 rows affected (0.15 sec)
# 客户端连接主服务器存储数据
[root@mysql53 ~]# mysql -h192.168.88.53 -uplj -p123456
mysql> create database gamedb;
Query OK, 1 row affected (0.12 sec)
mysql> create table gamedb.user(name char(10),class char(3));
Query OK, 0 rows affected (1.45 sec)
mysql> insert into gamedb.user values("yaya","nsd");
Query OK, 1 row affected (0.08 sec)
mysql> select * from gamedb.user;
+------+-------+
| name | class |
+------+-------+
| yaya | nsd   |
+------+-------+
1 row in set (0.00 sec)# 客户端连接从服务器查看数据
# -e 命令行下执行数据库命令
[root@mysql50 ~]# mysql -h192.168.88.53 -uplj -p123456 -e "select * from gamedb.user"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+-------+
| name | class |
+------+-------+
| yaya | nsd   |
+------+-------+

MySQL配置一主多从

环境：再准备一台服务器192.168.88.55
配置192.168.88.55为192.168.88.53主机的从服务器

# 1) 指定mysql55主机的server-id并重启数据库服务
[root@mysql55 ~]# yum -y  install mysql-server  mysql
[root@mysql55 ~]# vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server-id=55
[root@mysql55 ~]# systemctl  start mysqld
# 2）确保与主服务器数据一致。
# 在mysql53执行备份命令前查看日志名和偏移量 ，mysql55 在当前查看到的位置同步数据
mysql> show master status;
+----------------+----------+--------------+------------------+-------------------+
| File           | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------+----------+--------------+------------------+-------------------+
| mysql53.000002 |     1871 |              |                  |                   |
+----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
# 在主服务器存做完全备份
[root@mysql53 ~]# mysqldump -B gamedb > /root/gamedb.sql
# 将主服务器把备份文件拷贝给从服务器mysql55
[root@mysql53 ~]# scp /root/gamedb.sql root@192.168.88.55:/root/
[root@mysql55 ~]# mysql </root/gamedb.sql
# 3）在MySQL55主机指定主服务器信息
[root@mysql55 ~]# mysql
mysql> change master to master_host="192.168.88.53",master_user="repluser",master_password="123456",master_log_file="mysql53.000002",master_log_pos=1871;
Query OK, 0 rows affected, 8 warnings (0.88 sec)mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.05 sec)
mysql> show slave status\G
*************************** 1. row ***************************Slave_IO_State: Waiting for source to send eventMaster_Host: 192.168.88.53Master_User: repluserMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql53.000002Read_Master_Log_Pos: 1871Relay_Log_File: mysql55-relay-bin.000002Relay_Log_Pos: 322Relay_Master_Log_File: mysql53.000002Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 1871Relay_Log_Space: 533Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 53Master_UUID: bb19b901-52ca-11ee-86a6-525400605619Master_Info_File: mysql.slave_master_infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Replica has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0Network_Namespace: 
1 row in set, 1 warning (0.00 sec)

客户端测试配置

# 连接主服务器存储数据
[root@mysql50 ~]# mysql -h192.168.88.53 -uplj -p123456
mysql> insert into  gamedb.user values("tt","aid");
Query OK, 1 row affected (0.14 sec)
mysql> insert into  gamedb.user values("mm","uid");
Query OK, 1 row affected (0.13 sec)
# 在client50 分别连接2个从服务器查看数据
# 连接从服务器54查看数据
[root@mysql50 ~]# mysql -h192.168.88.54 -uplj -p123456 -e 'select * from gamedb.user'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+-------+
| name | class |
+------+-------+
| yaya | nsd   |
| tt   | aid   |
| mm   | uid   |
+------+-------+
# 连接从服务器55查看数据
[root@mysql50 ~]# mysql -h192.168.88.55 -uplj -p123456 -e 'select * from gamedb.user'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+-------+
| name | class |
+------+-------+
| yaya | nsd   |
| tt   | aid   |
| mm   | uid   |
+------+-------+