1. 主库的配置文件
my.cnf需要增加以下配置
# Should be unique
server-id = 1 log-bin = master-bin # Default=0 -- The number of days for automatic binary log file removal expire_logs_days = 14 # Default=1 -- 0:log&flush once per second(not guaranteed); 1:log&flush every commit; 2:log every commit & flush every second innodb_flush_log_at_trx_commit=1 # Default=1 -- Controls the number of binary log commit groups to collect before synchronizing the binary log to disk. 1 is the safest choice. sync_binlog = 1
/etc/my.cnf 例子
[mysqld] port = 3306 server-id = 1 log-bin = master-bin # Default=0 -- The number of days for automatic binary log file removal expire_logs_days = 14 # Default=1 -- 0:log&flush once per second(not guaranteed); 1:log&flush every commit; 2:log every commit & flush every second innodb_flush_log_at_trx_commit=1 # Default=1 -- Controls the number of binary log commit groups to collect before synchronizing the binary log to disk. 1 is the safest choice. sync_binlog = 1 # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links = 0 # With NO_ENGINE_SUBSTITUTION enabled, an error occurs and the table is not created or altered if the desired engine is unavailable. # Enable strict SQL mode for transactional storage engines, and when possible for nontransactional storage engines sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES join_buffer_size = 128M sort_buffer_size = 8M read_rnd_buffer_size = 4M key_buffer_size=32M max_allowed_packet=16M read_buffer_size = 4M tmp_table_size = 128M max_heap_table_size = 256M datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid
[mysql]
socket=/data/mysql/mysql.sock
在这一例中, 如果不配置socket文件路径, 在命令行连接时会出现错误
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
2. 创建用户, 准备数据
mysql> CREATE USER 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass'; mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.mydomain.com';
首先锁住主库, 注意运行这个语句的mysql命令行客户端不能退出, 否则会自动解除锁定
FLUSH TABLES WITH READ LOCK;
确定同步位置
SHOW MASTER STATUS;
此时可以用mysqldump将主库导出, 也可以停止主库, 将数据复制至从库, 具体的操作:
1) 新开mysql命令行, 停掉主库, 多实例环境下关闭 MySQL最好采用 mysqladmin 的形式, 避免把正在使用的实例关闭了
mysqladmin shutdown or service mysqld stop
2) 打包整个data目录
tar cf /tmp/db.tar ./data # or zip -r /tmp/db.zip ./data
3) 重启主库, 解除主库的锁
UNLOCK TABLES;
4) 将数据复制到从库的data目录
5) 删除目录下的auto.cnf文件, 否则会出现master and slave have equal MySQL server UUIDs错误
6) 设置目录属selinux性, 查看原数据库数据目录的selinux属性
ls -lZ /var/lib/mysql
复制过去后, 需要也修改为同样的属性, 否则启动会出错
chcon -Ru system_u mysql
chcon -Rt mysqld_db_t mysql
3. 配置从库后, 启动从库
从库的my.cnf只需添加server-id
[mysqld] server-id=2
# Accept updates from Master DB only, use super_read_only=1 for MySQL 5.7.8+
read_only = 1
# Specify the relay bin log file name. If not, it will use the host name.
relay-log=db02-relay-bin
4. 在从库中配置主从关系
mysql> CHANGE MASTER TO MASTER_HOST='master_host_name', MASTER_PORT=master_port, MASTER_USER='replication_user_name', MASTER_PASSWORD='replication_password', MASTER_LOG_FILE='recorded_log_file_name', MASTER_LOG_POS=recorded_log_position;
例如:
CHANGE MASTER TO MASTER_HOST='192.168.3.18', MASTER_PORT=6033, MASTER_USER='slaveuser', MASTER_PASSWORD='Slave.2016', MASTER_LOG_FILE='master-bin.000010', MASTER_LOG_POS=10080;
启动主从复制
START SLAVE;
5. 查看主从复制状态
# Slave_IO_Running 和 Slave_SQL_Running, 必须都为 Yes 才说明正常运行
SHOW SLAVE STATUSG;
6. 解决IO错误
# 查看是否限制 getsebool -a |grep mysql # 打开selinux限制 setsebool -P mysql_connect_any 1
将从库提升为主库
-- 执行下面的命令前, 必须先停止主从复制 STOP SLAVE; -- 清除主从复制的当前位置, 并且清除Master的连接信息. -- 此操作等同于RESET SLAVE后重启mysqld. -- 如果使用的是RESET SLAVE, 然后执行START SLAVE, 会导致从库从主库的最初位置开始复制, 与当前数据产生冲突 RESET SLAVE ALL; -- 执行后 SHOW SLAVE STATUS 再无输出 -- 需要修改/删除my.cnf中的只读设置, 如果有log-slaves-updates和read-only则要注释掉, 重启mysqld服务 -- 登录其他从库, STOP SLAVE, 将master指向新的主库, 然后START SLAVE
对从库备份时保持备份一致性
#!/bin/sh date = `date +%Y%m%d` mysqladmin --user=root --password=my_pwd stop-slave mysqldump --user=root --password=my_pwd --lock-all-tables --all-databases > /backups/mysql/backup-${date}.sql mysqladmin --user=root --password=my_pwd start-slave
参数:
--all-databases 导出所有数据库
--result-file=dump.sql 使用参数指定导出的文件, 而不是使用 > 重定向
--single-transaction 保证数据的一致性
--master-data=2 导出的sql中包含将当前db设为master所需的语句, 1:直接写成sql, 2:将语句写入注释如下. 当使用这个参数时, 会自动关闭 --lock-tables, 并打开 --lock-all-tables. 这个可以用于快速创建slave
-- -- Position to start replication or point-in-time recovery from -- -- CHANGE MASTER TO MASTER_LOG_FILE='master-bin.000013', MASTER_LOG_POS=21165664;
--dump-slave=2 导出的sql中包含将当前db的master设为master所需的语句, 取值含义同上, 但是里面的position就会使用master的而不是当前这个db的
--include-master-host-port 结合--dump-slave和这个, 能将db的地址端口一块儿写入CHANGE MASTER, 例如
ysqldump -h 192.168.3.14 -P 6099 -u root --all-databases --dump-slave=2 --include-master-host-port --result-file=dump_slave.sql
则会在dump出的sql中产生如下语句, 只需要自己补齐MASTER_USER='xxx', MASTER_PASSWORD='xxx' 就可以了, 这个dump可以用于在已有slave的情况下, 快速部署新的slave
-- -- Position to start replication or point-in-time recovery from (the master of this slave) -- -- CHANGE MASTER TO MASTER_HOST='192.168.3.18', MASTER_PORT=6099, MASTER_LOG_FILE='master-bin.000013', MASTER_LOG_POS=21178093;
备份从库时需要的权限
在只做普通dump时, 需要SELECT, LOCK TABLES, SHOW VIEW, TRIGGER
但是: 如果需要使用--dump-slave 或 --master-data 这类需要stop/start slave的选项时, 需要 SUPER, RELOAD 权限. (这个很危险...)
mysql> SHOW GRANTS FOR 'db_dump'@'192.168.1.0/255.255.255.0'; +------------------------------------------------------------------------------------------------------+ | Grants for db_dump@192.168.1.0/255.255.255.0 | +------------------------------------------------------------------------------------------------------+ | GRANT RELOAD, SUPER, REPLICATION CLIENT ON *.* TO 'db_dump'@'192.168.1.0/255.255.255.0' | | GRANT SELECT, LOCK TABLES, SHOW VIEW, TRIGGER ON `somedb`.* TO 'db_dump'@'192.168.1.0/255.255.255.0' | +------------------------------------------------------------------------------------------------------+