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 =

    log-bin = mysql-bin

    expire-logs-days =   # 设置二进制日志过期的天数

    max_binlog_size = 512M  # 二进制日志滚动的阀值

slave

    server-id =

    expire-logs-days =

    max-relay-log-size = 512M

    relay-log = mysql-relay-bin

    relay_log_recovery =   # 修复中继日志

    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=;

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{,,}

chown -R mysql:mysql /storage/data/mysql{,,}

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]

#  mysql1

port            =

socket          = /tmp/mysql3306.sock

basedir = /storage/server/mysql

datadir = /storage/data/mysql1

character-set-server=utf8

collation-server=utf8_unicode_ci

server-id =

expire-logs-days =

max-relay-log-size = 512M

relay-log = mysql1-relay-bin

relay_log_recovery =

replicate_wild_ignore_table=mysql.%

[mysqld2]

#  mysql2

port            =

socket          = /tmp/mysql3307.sock

basedir = /storage/server/mysql

datadir = /storage/data/mysql2

character-set-server=utf8

collation-server=utf8_unicode_ci

server-id =

expire-logs-days =

max-relay-log-size = 512M

relay-log = mysql2-relay-bin

relay_log_recovery =

replicate_wild_ignore_table=mysql.%

[mysqld3]

#  mysql3

port            =

socket          = /tmp/mysql3308.sock

basedir = /storage/server/mysql

datadir = /storage/data/mysql3

character-set-server=utf8

collation-server=utf8_unicode_ci

server-id =

expire-logs-days =

max-relay-log-size = 512M

relay-log = mysql3-relay-bin

relay_log_recovery =

replicate_wild_ignore_table=mysql.%

mysql主从常见错误

error 1062

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

解决方案：从库

mysql> stop slave;

mysql> set global sql_slave_skip_counter=;

mysql> start slave;

巴特西

MySQL复制配置（多主一从）

最新文章

热门文章