实验环境
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
若无特殊注明,文章均为本站原创或整理发布。
转载请注明本文地址:https://om.fangxiaoxiong.com/1842.html
通告: MySQL-MHA方案实践 – 瓜丢夏