记录mysql主从配置相关命令

Mysyql主从设置相关命令。

主服务器配置

修改配置文件:

[mysqld]
server_id=1
#binlog日志格式:STATEMENT，ROW，MIXED。mysql默认采用STATEMENT，建议使用MIXED
binlog_format=MIXED
log-bin = mysql-bin    
#需要复制的数据库名，如果复制多个数据库，重复设置这个选项即可
binlog-do-db = db1
binlog-do-db = db2

#不需要复制的数据库名，重复设置这个选项即可
replicate-ignore-db=mysql
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
#将从服务器从主服务器收到的更新记入到从服务器自己的二进制日志文件中
log-slave-updates
#控制binlog的写入频率。每执行多少次事务写入一次(这个参数性能消耗很大，但可减小MySQL崩溃造成的损失)
sync_binlog = 1
#这个参数一般用在主主同步中，用来错开自增值, 防止键值冲突
auto_increment_offset = 1
#这个参数一般用在主主同步中，用来错开自增值, 防止键值冲突
auto_increment_increment = 1
#二进制日志自动删除的天数，默认值为0,表示“没有自动删除”，启动时和二进制日志循环时可能删除
expire_logs_days = 7
#将函数复制到slave
log_bin_trust_function_creators = 1

需要执行的sql:

-- 创建一个用于主从复制的用户
-- 创建用户
create user 'slave1'@'%' identified by '123456';
-- 授权 *.*表示所有库
GRANT SELECT, PROCESS, FILE, SUPER, REPLICATION CLIENT, REPLICATION SLAVE, RELOAD ON *.* TO 'slave1'@'%';
FLUSH PRIVILEGES;

-- 下面的命令用于查看状态
-- 查验权限
SHOW GRANTS FOR 'slave1'@'%';
-- 查看主服务器状态
show master status;
-- 查看数据库信息
SHOW VARIABLES LIKE 'server_id';
SHOW VARIABLES LIKE 'version';
SHOW VARIABLES LIKE '%log_bin%';

从服务器配置

修改配置文件:

[mysqld]
server_id = 2
log-bin= mysql-bin
read-only=1
replicate-ignore-db=mysql
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema

需要执行的sql:

-- 配置连接主服务的信息
-- 从服务器配置
change master to
    master_host ='192.168.1.200',
    master_port =3306,
    master_user ='slave1',
    master_password ='123456',
    master_log_file ='mysql-bin.000002',
    master_log_pos = 16605505,
    master_connect_retry =30;

-- 下面的命令用于查看从服务器状态
-- 启动服务
start slave;
-- 停止服务
stop slave;
-- 重置同步状态
reset slave;

-- 查看从服务器状态
show slave status;
-- 查看数据库信息
SHOW VARIABLES LIKE 'server_id';
SHOW VARIABLES LIKE 'version';

master_log_file和master_log_pos两个参数的值需与主服务器上的对应，在主服务器执行show master status \G可查看,File字段对应master_log_file,Position对应master_logs_pos。

其他相关sql

手动加锁:

-- 备份数据库
-- 全局加锁
flush tables with read lock;
-- 解锁
UNLOCK TABLES;