mysql:主从复制

---

1. 主从复制

2. 实验

server1：主库 server2：从库

安装mysql
server1

1. [root@server1 ~]# tar xf mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar 
2. [root@server1 ~]# yum install mysql-community-client-5.7.28-1.el7.x86_64.rpm mysql-community-common-5.7.28-1.el7.x86_64.rpm mysql-community-libs-5.7.28-1.el7.x86_64.rpm mysql-community-libs-compat-5.7.28-1.el7.x86_64.rpm mysql-community-server-5.7.28-1.el7.x86_64.rpm -y
注意：按顺序安装

server2

1. [root@server2 ~]# tar xf mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar 
2. [root@server2 ~]# yum install mysql-community-client-5.7.28-1.el7.x86_64.rpm mysql-community-common-5.7.28-1.el7.x86_64.rpm mysql-community-libs-5.7.28-1.el7.x86_64.rpm mysql-community-libs-compat-5.7.28-1.el7.x86_64.rpm mysql-community-server-5.7.28-1.el7.x86_64.rpm -y

配置文件

server1

1. [root@server1 ~]# vim /etc/my.cnf
log-bin=mysql-bin
server-id=1
2. [root@server1 ~]# systemctl start mysqld
3. [root@server1 ~]# cat /var/log/mysqld.log | grep password
2020-03-07T14:33:35.732895Z 1 [Note] A temporary password is generated for root@localhost: XRhe=hf3ld7Z
4. [root@server1 ~]# mysql_secure_installation  # 重新设置密码
5. [root@server1 ~]# mysql -uroot -p  # 登陆成功
Enter password: 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)
mysql> grant replication slave on *.* to repl@'172.25.60.%' identified by 'Westos+001';  #开启slave线程，允许172.25.60.%这个网段的人复制主库
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      935 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

server2

1. [root@server2 ~]# vim /etc/my.cnf
server-id=2
2. [root@server2 ~]# systemctl start mysqld
3. [root@server2 ~]# cat /var/log/mysqld.log |grep password
2020-03-08T01:58:44.009974Z 1 [Note] A temporary password is generated for root@localhost: :E0GVOtYeGa_
4. [root@server2 ~]# mysql_secure_installation 
new password:Westos+001
5. [root@server2 ~]# mysql -uroot -p
Enter password: 

mysql> change master to master_host='172.25.60.253',
    -> master_user='repl', master_password='Westos+001',
    -> master_log_file='mysql-bin.000002',
    -> master_log_pos=935;
Query OK, 0 rows affected, 2 warnings (0.58 sec)
mysql> start slave;
Query OK, 0 rows affected (0.11 sec)
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.60.253
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 935
               Relay_Log_File: server2-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

测试：
server1

server1：
mysql> create database linux;
Query OK, 1 row affected (0.12 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| linux              |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)
mysql> use linux;
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql> create table userinfo(
    -> username varchar(10) not null,
    -> password varchar(25) not null);
Query OK, 0 rows affected (1.20 sec)

mysql> show tables;
+-----------------+
| Tables_in_linux |
+-----------------+
| userinfo        |
+-----------------+
1 row in set (0.00 sec)
mysql> desc userinfo;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(10) | NO   |     | NULL    |       |
| password | varchar(25) | NO   |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> insert into userinfo values ('user1','123');
Query OK, 1 row affected (0.65 sec)

mysql> select * from userinfo;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 123      |
+----------+----------+
1 row in set (0.00 sec)

server2：查看是否有server1创建的库和表

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

mysql> use linux;
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_linux |
+-----------------+
| userinfo        |
+-----------------+
1 row in set (0.00 sec)

mysql> select * from userinfo;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 123      |
+----------+----------+
1 row in set (0.00 sec)

查看binlog日志：

[root@server1 ~]# cd /var/lib/mysql
[root@server1 mysql]# mysqlbinlog mysql-bin.000002   # 2日志中记录了主库的所有操作

查看中继日志：

[root@server2 ~]# cd /var/lib/mysql
[root@server2 mysql]# cat relay-log.info
7
./server2-relay-bin.000002
979
mysql-bin.000002
1594
0
0
1