mysql主从复制实验 -- 2020/8/14

mysql主从复制实验

实验准备

1、两台虚拟机（centos7-2C4G）、两台虚拟机安装好mysql
2、两台虚拟机一台做主master192.168.195.136一台做从slave192.168.195.137
3、在主mysql服务器上开启二进制日志

# log_bin
log-bin
server-id=1

4、在从mysql服务器上开启二进制日志

# log_bin
log-bin
server-id=2

在主服务器上新建并且授权一个用户，用于复制二进制日志

grant replication slave on *.* to 'ekko'@'192.168.195.137' identified by 'Tzkwan1314=';
Query OK, 0 rows affected, 1 warning (0.02 sec)

备份出主服务器上的数据，然后到导入到从服务器上

mysqldump -uroot -p'STzkwan1314='  --all-databases > /backup/all_db.sql
scp all_db.sql root@192.168.195.137:/root

然后再从服务器上导入

mysql -uroot -p'Tzkwan1314=' <all_db.sql 

在主服务器上查看master状态，更新flush

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

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

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

在从服务器上配置master信息

mysql> change master to master_host='192.168.195.136',
    -> master_user='ekko',
    -> master_password='Tzkwan1314=',
    -> master_port=3306,
    -> master_log_file='mysql-sc-bin.000005',
    -> master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

查看slave状态信息

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: 192.168.195.136
                  Master_User: ekko
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-sc-bin.000005
          Read_Master_Log_Pos: 154
               Relay_Log_File: slave-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-sc-bin.000005
             Slave_IO_Running: No
            Slave_SQL_Running: No

在slave上开启复制功能

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

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.195.136
                  Master_User: ekko
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-sc-bin.000005
          Read_Master_Log_Pos: 154
               Relay_Log_File: slave-relay-bin.000002
                Relay_Log_Pos: 323
        Relay_Master_Log_File: mysql-sc-bin.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

在master上新建库和表，在slave检验

mysql> create database tanzikun;
Query OK, 1 row affected (0.01 sec)

mysql> use tanzikun;
Database changed
mysql> create table t1(id int);
Query OK, 0 rows affected (0.09 sec)

mysql> insert into t1 values(1),(2);
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| game               |
| mysql              |
| performance_schema |
| sys                |
| tanzikun           |
+--------------------+
6 rows in set (0.01 sec)

mysql> use tanzikun;
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_tanzikun |
+--------------------+
| t1                 |
+--------------------+
1 row in set (0.00 sec)

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

在slave上查看master.info\relay-log.info

[root@slave mysql]# cat master.info
25
mysql-sc-bin.000005
761
192.168.195.136
ekko
Tzkwan1314=
3306
60
0

[root@slave mysql]# cat relay-log.info
7
./slave-relay-bin.000002
930
mysql-sc-bin.000005
761
0
0
1