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 event
                  Master_Host: 192.168.88.53
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql53.000002
          Read_Master_Log_Pos: 667
               Relay_Log_File: mysql54-relay-bin.000002
                Relay_Log_Pos: 322
        Relay_Master_Log_File: mysql53.000002
             Slave_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: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 667
              Relay_Log_Space: 533
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_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: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 53
                  Master_UUID: bb19b901-52ca-11ee-86a6-525400605619
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_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 event
                  Master_Host: 192.168.88.53
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql53.000002
          Read_Master_Log_Pos: 1871
               Relay_Log_File: mysql55-relay-bin.000002
                Relay_Log_Pos: 322
        Relay_Master_Log_File: mysql53.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1871
              Relay_Log_Space: 533
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_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: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 53
                  Master_UUID: bb19b901-52ca-11ee-86a6-525400605619
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_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   |
+------+-------+