MySQL主从复制实践

实验环境

Master:192.168.244.139
Slave:192.168.244.138
 

1.主库开始binlog,主从server-id值不能相同

[root@CentOS7-MASTER ~]# egrep "log-bin|server-id" /etc/my.cnf
log-bin=/data/binlog/mysql-bin
server-id = 136

2.检查参数更改情况

[root@CentOS7-MASTER ~]# mysql -e "show variables like 'log_bin'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+

3.在主库上建立主从复制的账号

mysql> grant replication slave on *.* to 'rep'@'192.168.244.%' identified by 'test123';   #replication slave 为允许slave同步的必备权限
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select user,host from mysql.user;
+-----------+---------------+
| user      | host          |
+-----------+---------------+
| rep       | 192.168.244.% |
| mysql.sys | localhost     |
| root      | localhost     |
+-----------+---------------+
3 rows in set (0.00 sec)

4.对主库锁表只读后进行备份

mysql> flush table with read lock;
Query OK, 0 rows affected (0.00 sec)

5.锁表后查看主库状态

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |     1888 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
#可以查看binlog文件名以及位置

6.导出主库数据

[root@CentOS7-MASTER ~]# mysqldump -A -B |gzip >/root/backup/bak_$(date +%F).sql.gz #使用-A参数会覆盖从库的root密码
[root@CentOS7-MASTER ~]# ll backup
total 208
-rw-r--r--. 1 root root 209636 Jan 14 23:32 bak_2020-01-14.sql.gz

7.再次检查show master status;导出数据库期间,确保没有数据插入,导出后解锁主库

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

8.推送主库数据库到从库

[root@CentOS7-MASTER ~]# scp -rp backup/bak_2020-01-14.sql.gz  root@192.168.244.138:/root/backup
The authenticity of host '192.168.244.138 (192.168.244.138)' can't be established.
ECDSA key fingerprint is SHA256:XOkXl1lwNU+5IYSen4q0OTjvuyZfBCZluSojQp1nqAc.
ECDSA key fingerprint is MD5:cd:a3:cc:ce:77:93:30:73:ff:ab:63:0e:22:06:c1:56.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.244.138' (ECDSA) to the list of known hosts.
root@192.168.244.138's password:
bak_2020-01-14.sql.gz                                                                                    100%  205KB   3.7MB/s   00:00
[root@CentOS7 ~]#

9.从库无需开启binlog(如果级联复制的话需要开启),检查状态

[root@localhost-SALVE ~]# mysql -e "show variables like 'log_bin';"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | OFF   |
+---------------+-------+
[root@localhost ~]# mysql -e "show variables like 'server_id';"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 244   |
+---------------+-------+

10.恢复主库备份数据库到从库

[root@localhost-SALVE ~]# zcat backup/bak_2020-01-14.sql.gz |mysql

11.从库配置复制参数

mysql> change master to
    -> master_host='192.168.244.139',
    -> master_port=3306,
    -> master_user='rep',
    -> master_password='test123',
    -> master_log_file='mysql-bin.000003',
    -> master_log_pos=1888;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
[root@localhost ~]# cat /data/mysql/master.info
25
mysql-bin.000003
1888
192.168.244.139
rep
test123
3306
....

12.启动从库复制开关

[root@localhost-SALVE ~]# mysql -e "start slave;"
[root@localhost-SALVE ~]# mysql -e "show slave status\G;"
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.244.139
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 2182
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 614
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
.....

13.测试,主库创建数据

[root@CentOS7-MASTER ~]# mysql -e "create database test11;"  #主库

[root@localhost-SLAVE ~]# mysql -e "show databases;"   #从库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
| test11             |
+--------------------+


「 文章如果对你有帮助,请点个赞哦^^ 」 

0

这篇文章有一条评论

评论关闭。