数据库主从复制案例

数据库主从复制案例

环境：

• master：192.168.154.136

• slave01:192.168.154.138

前提条件

• 主库和从库的数据库的版本保持一致；
• 主从复制集群中每个数据库实例的sarver-id 的值不能重复；
• 要开启归档日志并且归档日志的格式选择为row方式；

mysql> show variables like '%binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.37 sec)

具体步骤

## 在主库上，启用二进制日志记录并设置SERVER-ID
[root@liyating ~]# vim  log10.txt
server_id=143  #主服务器的server_id
log_bin=/var/lib/mysql/binlogs/master
log_bin_index=/var/lib/mysql/binlogs/master.index
## 重启服务
[root@liyating ~]# systemctl restart mysqld.service 

## 查看主库当前binary log文件名和位置（position）
mysql> show master status;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    8
Current database: *** NONE ***

+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000011 |      155 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)

## 创建一个复制用户，从库使用此账号连接到主库
mysql> create user 'binlog_user'@'%' identified with mysql_native_password by '123.com';
Query OK, 0 rows affected (0.01 sec)

## 防火墙放行
[root@liyating ~]# firewall-cmd --add-service=mysql --permanent 
success
[root@liyating ~]# firewall-cmd --reload 
success

## 从库上，设置唯一的SERVER_ID 选项
[root@slave01 ~]# vim /etc/my.cnf
server_id=144  
log_bin=/var/lib/mysql/binlogs/slave01
log_bin_index=/var/lib/mysql/binlogs/slave01.index
## 重启服务
[root@slave01 ~]# systemctl restart mysqld.service 

## 主库授权
mysql> create user 'root'@'192.168.149.%' identified with mysql_native_password by '123.com';
Query OK, 0 rows affected (0.00 sec)
mysql> grant all on *.* to 'root'@'192.168.149.%';
Query OK, 0 rows affected (0.01 sec)

## 在从库进行备份
[root@slave01 ~]# mysqldump -h 192.168.149.143 -uroot --password=123.com \
> --all-databases --routines --events --single-transaction --master-data > dump.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: Got error: 1045: Access denied for user 'root'@'192.168.149.144' (using password: YES) when trying to connect
[root@slave01 ~]# ls -lh dump.sql 
-rw-r--r--. 1 root root 0 1月   3 08:53 dump.sql

## 在从库上，待备份完成后恢复此备份
[root@slave01 ~]# mysql -uroot -p123.com < dump.sql
mysql: [Warning] Using a password on the command line interface can be insecure.

## 恢复备份后执行以下命令
mysql> change master to master_host='192.168.149.143',
    -> master_user='binlog_user',master_password='123.com',
    -> master_log_file='master.000009',master_log_pos=679;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

## 在从库上，执行start slave命令
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

## 查看复制的状态
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.149.143
                  Master_User: binlog_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master.000009
          Read_Master_Log_Pos: 679
               Relay_Log_File: slave01-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: master.000009
             Slave_IO_Running: No
            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: 679
              Relay_Log_Space: 155
              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: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 13117
                Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 200103 08:57:43
     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 (0.00 sec)

## 如果是克隆的虚拟机
[root@slave01 data]# cat auto.cnf 
[auto]
server-uuid=ff20c617-2779-11ea-8920-000c29903853
[root@slave01 data]# mv auto.cnf auto.cnf.bak
[root@slave01 data]# systemctl restart mysqld.service 

## 主库上创建库和表
mysql> create database banks;
Query OK, 1 row affected (0.01 sec)
mysql> create table account(name varchar(20),amount float); 
Query OK, 0 rows affected (0.36 sec)

mysql> insert into account values ('zhangsan',50000); 
Query OK, 1 row affected (0.16 sec)

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

## 从库上查看
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| bank               |
| banks              |
| company            |
| employees          |
| information_schema |
| my.contacts        |
| mysql              |
| performance_schema |
| school             |
+--------------------+
12 rows in set (0.00 sec)