共计 3522 个字符,预计需要花费 9 分钟才能阅读完成。
MySQL 5.7 主主备份配置
1. 主要配置步骤
主库配置步骤:
1、GRANT 创建用户并授权,ip 为从服务器的 ip, 本句含义是为创建一个用户名为 uname,密码为 upwd 的用户,这个用户只能从 192.168.1.111 上进行访问
mysql> grant replication slave on *.* to 'repl_user'@'192.168.3.115' identified by 'zcxc123';
2 Query OK, 0 rows affected (0.01 sec)
2、修改 my.cnf 配置文件如下:
log-bin=mysql-bin # 启动二进制文件 2 server_id=1
# 服务器 ID
3、重启 mysql
此时可以查看主服务器 binlog 日志 position 值
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000080
Position: 154
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
4、锁定所有表 mysql> FLUSH TABLES WITH READ LOCK;
5、备份表 [root@localhost mysql]# mysqldump -uroot -p --all-databases -l -F >all_db.sql
6、解锁 mysql> UNLOCK TABLES;
7、把数据传到从库 (192.168.3.115) # scp all_db.sql root@192.168.1.111:/tmp
从库配置步骤:
1、修改从服务器 my.cnf 配置文件
log_bin = mysql
server_id = 2
2、重启 mysql 服务器
service mysqld restar
3、导入主备份文件
# mysql -uroot -p </tmp/all_db.sql
4、同步 binlog 日志
mysql> reset slave;
Query OK, 0 rows affected (0.00 sec)
注:master_user='repl_user',master_password='zcxc123' 是主库第一步 grant replication 语句设置的
master_log_file='mysql-bin.000080',master_log_pos=154 是主库第三步 show master status\ G 语句获取的
mysql> change master to master_host='192.168.3.116',master_user='repl_user',master_password='zcxc123',master_log_file='mysql-bin.000080',master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.03 sec)
mysql> start slave;
Query OK, 0 rows affected (0.02 sec)
主主配置就是,按照以上步骤,把上面从库按主库配置一遍。再配置时 不用备份表了。
2. 配置文件
配置文件 1
-bash-4.1# more /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
validate_password=OFF
server-id=1
user=mysql
log-bin=mysql-bin
log-slave-updates
slave-skip-errors=all
sync_binlog=1
auto-increment-increment = 1
auto-increment-offset = 1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
relay_log=/var/lib/mysql/mysql-relay-bin
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
-bash-4.1#
配置文件 2
-bash-4.1# more /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
validate_password=OFF
server-id=2
log-bin=mysql-bin
log-slave-updates
slave-skip-errors=all
sync_binlog=1
auto_increment_increment=2
auto_increment_offset=1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
relay_log=/var/lib/mysql/mysql-relay-bin
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
本文永久更新链接地址 :http://www.linuxidc.com/Linux/2016-11/136831.htm
正文完
星哥玩云-微信公众号