mysql主从加MHA+keepalived部署(二) —— mysql主从部署

相关文章：

mysql主从加MHA+keepalived部署(一)——mysql安装：

https://blog.youkuaiyun.com/royzhang7/article/details/100031390

mysql主从加MHA+keepalived部署(三)——mha部署安装: 

https://blog.youkuaiyun.com/royzhang7/article/details/100031601

mysql主从加MHA+keepalived部署(四)——keepalived部署安装：

https://blog.youkuaiyun.com/royzhang7/article/details/100031832

 

 

--环境
虚机A: 192.168.3.3 redhat7  --主
虚机B: 192.168.3.4 redhat7  --从

--安装mysql版本
5.7.26

--修改主库（master)参数文件，添加bin-log相关参数和gtid相关参数

vi /etc/my.cnf

#binlog
server-id=1
binlog-format=MIXED
log-bin=/data/mysql/log/mysql-bin.log
max_binlog_size=200M   
binlog_cache_size=4M
max_binlog_cache_size=300M
expire_logs_days=30

gtid_mode=ON
enforce_gtid_consistency=ON
log-slave-updates=1

--重启主库
systemctl restart mysqld

--主库添加同步用户
mysql> grant replication slave on *.* to 'repl'@'192.168.3.4' identified by 'repl';

mysql> show grants for 'repl'@'192.168.3.4';

+--------------------------------------------------------+
| Grants for repl@192.168.3.4                            |
+--------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.3.4' |
+--------------------------------------------------------+

--主库创建全备，并记录bin-log位置

mysqldump -uroot -pmysql --skip-lock-tables --single-transaction --flush-logs --hex-blob --master-data=2 --events  --routines  --all-databases  > /data/mysqldb_all_`date +"%Y%m%d"`.bak

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

--将主库备份文件传至备库
scp mysqldb_all*.bak 192.168.3.4:/data/

--修改从库（slave)参数文件，添加bin-log相关参数和gtid相关参数

vi /etc/my.cnf

#binlog
server-id=2
binlog-format=MIXED
log-bin=/data/mysql/log/mysql-bin.log
max_binlog_size=200M   
binlog_cache_size=4M
max_binlog_cache_size=300M
expire_logs_days=30

gtid_mode=ON
enforce_gtid_consistency=ON
log-slave-updates=1

--重启从库
systemctl restart mysqld

--将备份导入从库
mysql -uroot -pmysql </data/mysqldb_all_20190719.bak

--从库配置同步参数
mysql> 
CHANGE MASTER TO  
MASTER_HOST='192.168.3.3', 
MASTER_PORT=3306, 
MASTER_USER='repl', 
MASTER_PASSWORD='repl', 
MASTER_LOG_FILE='mysql-bin.000002', 
MASTER_LOG_POS=154; 

如果启用了gtid方式复制
可以使用下面参数同步
change master to master_host='192.168.3.3', master_user='repl',master_password='repl',master_port=3306,master_auto_position=1;

--从库开启同步
mysql>  start slave;

--从库查看同步状态
mysql>  show slave status\G

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0

上面两个进程状态是yes就ok了

--主从同步测试
主库创建测试库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> create database test;
Query OK, 1 row affected (0.02 sec)

从库查看已同步
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.01 sec)

--从库创建复制账号，为了主从切换后使用
grant replication slave on *.* to 'repl'@'192.168.3.3' identified by 'repl';
flush privileges;