MySQL复制配置（多主一从）

复制多主一从

 

replicaion

原理

复制有三个步骤：（分为三个线程 slave：io线程 sql线程　master：io线程）

1、master将改变记录到二进制日志(binary log)中（这些记录叫做二进制日志事件，binary log events）

2、slave将master的binary log events拷贝到它的中继日志(relay log)

3、slave读取中继日志中的事件，将其重放到slave数据之上

日志解释

从库先通过io线程读取主库的二进制文件（Master_Log_File）和位置（Read_Master_Log_Pos）然后缓存到本地（从库服务器）的中继文件（Relay_Log_File）中并记录已经读取到的位置（Relay_Log_Pos），再通过从库的sql线程去读取中继文件（Relay_Log_File），这个sql线程执行会记录已经执行到了哪个文件（Relay_Master_Log_File）和哪个位置（Exec_Master_Log_Pos）。

配置复制

1、在master创建复制账号　　

GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO repl@'192.168.0.%' IDENTIFIED BY 'PASSWD';

2、配置master和slave

master
    server-id = 1
    log-bin = mysql-bin
    expire-logs-days = 10  # 设置二进制日志过期的天数
    max_binlog_size = 512M  # 二进制日志滚动的阀值

slave
    server-id = 2
    expire-logs-days = 10
    max-relay-log-size = 512M
    relay-log = mysql-relay-bin
    relay_log_recovery = 1  # 修复中继日志
    replicate_wild_ignore_table = mysql.%  # 过滤哪些主库不复制

3、通知slave连接到master并从master复制数据

CHANGE MASTER TO MASTER_HOST='master',MASTER_USER='repl',MASTER_PASSWORD='passwd',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=0; 

4、启动复制，查看状态

slave
　　START SLAVE

show slave status\G

在输出信息中查看I/O线程和SQL线程的状态值（YES为正常，NO为错误）

　　Slave_IO_Running: Yes

　　Slave_SQL_Running: Yes

 

mysqld_multi

与安装单实例一样，只是要初始化多个数据目录对应相应的实例

mkdir -p /storage/data/mysql{1,2,3}
chown -R mysql:mysql /storage/data/mysql{1,2,3}
scripts/mysql_install_db --user=mysql --datadir=/storage/data/mysql1
scripts/mysql_install_db --user=mysql --datadir=/storage/data/mysql2
scripts/mysql_install_db --user=mysql --datadir=/storage/data/mysql3

添加多实例管理用户

GRANT SHUTDOWN ON *.* TO 'multi'@'localhost' IDENTIFIED BY 'multi'

mysqld_multi

Usage: mysqld_multi [OPTIONS] {start|stop|report} [GNR,GNR,GNR...]

my.cnf

[mysqld_multi]
mysqld = /storage/server/mysql/bin/mysqld_safe
mysqladmin = /storage/server/mysql/bin/mysqladmin
user = multi
password = multi
log = /storage/data/mysqld_multi.log

[mysqld1]
# 3306 mysql1
port            = 3306
socket          = /tmp/mysql3306.sock
basedir = /storage/server/mysql
datadir = /storage/data/mysql1
character-set-server=utf8
collation-server=utf8_unicode_ci
server-id = 2
expire-logs-days = 10
max-relay-log-size = 512M
relay-log = mysql1-relay-bin
relay_log_recovery = 1
replicate_wild_ignore_table=mysql.%

[mysqld2]
# 3307 mysql2
port            = 3307
socket          = /tmp/mysql3307.sock
basedir = /storage/server/mysql
datadir = /storage/data/mysql2
character-set-server=utf8
collation-server=utf8_unicode_ci
server-id = 2
expire-logs-days = 10
max-relay-log-size = 512M
relay-log = mysql2-relay-bin
relay_log_recovery = 1
replicate_wild_ignore_table=mysql.%

[mysqld3]
# 3308 mysql3
port            = 3308
socket          = /tmp/mysql3308.sock
basedir = /storage/server/mysql
datadir = /storage/data/mysql3
character-set-server=utf8
collation-server=utf8_unicode_ci
server-id = 2
expire-logs-days = 10
max-relay-log-size = 512M
relay-log = mysql3-relay-bin
relay_log_recovery = 1
replicate_wild_ignore_table=mysql.%

mysql主从常见错误

error 1062

错误原因：主键冲突 出现这种情况就是从库出现插入操作 主库又重新来了一遍 iothread没问题 sqlthread出错

解决方案：从库

mysql> stop slave;
mysql> set global sql_slave_skip_counter=1;
mysql> start slave;