mysql主从复制运维_mysql主从复制

mysql主从复制

1、基本主从服务器配置

(1)主服务器配置

[client]

port = 3306

socket = /tmp/mysql.sock

[mysqld]

user = mysql

innodb_buffer_pool_size = 128M

log_bin = master-log  ===========

max_binlog_size = 64M

binlog_format = mixed   ========

basedir = /usr/local/mysql

datadir = /database/mydata

port = 3306

server_id = 1  ==========

socket = /tmp/mysql.sock

log_error = /database/mydata/server1.err

character_set_server = utf8

explicit_defaults_for_timestamp = on

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

数据库初始化

./scripts/mysql_install_db --user=mysql --datadir=/database/mydata

给从服务器授权

mysql> grant replication slave on *.* to 'repuser'@'10.0.5.151' identified by 'aixocm';

mysql> flush privileges;

mysql> show master status\G

(2)从服务器配置

[client]

port = 3306

socket = /tmp/mysql.sock

[mysqld]

user = mysql

innodb_buffer_pool_size = 128M

log_bin = slave-log  ============

max_binlog_size = 64M

log_slave_updates = on =====

binlog_format = mixed  =====主从服务器才需要这两项，从服务器不需要

relay_log = relay-bin  ============

basedir = /usr/local/mysql

datadir = /database/mydata

port = 3306

server_id = 2  ======

socket = /tmp/mysql.sock

log_error = /database/mydata/server1.err

character_set_server = utf8

explicit_defaults_for_timestamp = on

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

数据库初始化

# ./scripts/mysql_install_db --user=mysql --datadir=/database/mydata

设置主服务器相关信息

mysql> change master to master_host='10.0.5.150',master_user='repuser',master_password='aixocm',master_log_file='master-log.000003',master_log_pos=409,master_port=3306;

mysql> start slave;

mysql> show slave status\G

mysql> stop slave;

(3)二进制日志操作

# mysqlbinlog master-log.000001

# mysqlbinlog --start-datetime='2015-07-13 7:10:0' master-log.000003

# mysqlbinlog --start-datetime='2015-07-13 7:10:0' --stop-datetime='2015-07-13 7:33:0' master-log.000003

# mysqlbinlog --start-position=330 master-log.000003

# mysqlbinlog --start-position=330 --stop-position=1100 master-log.000003

# mysqlbinlog --start-position=330 --stop-position=1100 master-log.000003 | mysql -u root \\恢复指定二进制日志的内容

mysql> show master logs;

mysql> show binary logs;

mysql> show binlog events in 'master-log.000003' limit 20;

mysql> show binlog events in 'master-log.000003' from 409 limit 10;

mysql> purge master logs to 'master-log.000002';  \\删除指定编号之前的日志

mysql> purge master logs before '2015-07-22 08:00:00';

(4)设置忽略的数据库

主服务器设置

binlog_do_db = sxjy          \\记录二进制日志的数据库

binlog_ignore_db = test       \\不记录二进制日志的数据库

binlog_ignore_db = teach

从服务器设置

replicate_do_db = sxjy        \\设置默认进行二进制日志复制的数据库

replicate_ignore_db = test    \\不进行二进制日志复制的数据库

replicate_ignore_db = teach

replicate_ignore_db = mysql

replicate_ignore_db = information_schema

replicate_do_table = sxjy.stu        \\设置进行更新的表

replicate_ignore_table = sxjy.class   \\不进行更新的表

从服务器建议设置

replicate_ignore_db = test            \\不进行二进制日志复制的数据库

replicate_ignore_db = mysql

replicate_ignore_db = information_schema

replicate_wild_do_table = sxjy.stu    \\复制指定的数据库或表的二进制日志

replicate_wild_do_table = sxkj.%

replicate_wild_ignore_table = sxjy.class \\不复制指定的数据库或表的二进制日志

练习:先搭建一台mysql数据库服务器,不启用二进制日志,然后启动服务器，先创建一个sxjy数据库,

在sxjy数据库下创建stu和teach两个表,字段自己添加3-4个,然后插入4-5个记录。然后将

服务器改为主从结构,要求原先的服务器做主服务器，且在从服务器上要有主服务器上原来的数据,

主从结构搭建好后,以后添加的数据能够自动同步到从服务器,主从服务器数据要完全一致。

2、基于GTID的事务复制

(1)能够基于数据库进行多线程复制(要求2个或2个以上的数据库进行同步)

(2)能够自动判断要复制的位置

(3)不能支持非事务存储引擎,如MyISAM

(4)不支持create table ... select from ... 语句

主服务器配置

log_slave_update = on

gtid_mode = on   \\开启GTID模式

enforce_gtid_consistency = on      \\强制GTID的一致性

master_info_repository =TABLE      \\主服务器信息的记录方式(TABLE或FILE)

relay_log_info_repository = TABLE  \\中继日志信息的记录方式

sync_master_info = 1               \\同步主数据库信息

slave_parallel_workers = 4         \\从服务器的sql线程数,和要复制的数据库相同

binlog_checksum =CRC32             \\二进制日志的校验方式

master_verify_checksum = 1         \\主服务器启用校验

slave_sql_verify_checksum = 1      \\从服务器启用校验

binlog_rows_query_log_events = 1   \\二进制日志详细记录事件

report_port = 3306                 \\提够复制的报告端口,和数据库端口一致

report_host = 10.0.5.150           \\提供复制报告的主机,设为本机地址

mysql> grant replication slave on *.* to 'repuser'@'10.0.5.151' identified by 'aixocm';

mysql> flush privileges;

从服务器配置

log_slave_update = on

gtid_mode = on   \\开启GTID模式

enforce_gtid_consistency = on      \\强制GTID的一致性

master_info_repository =TABLE      \\主服务器信息的记录方式(TABLE或FILE)

relay_log_info_repository = TABLE  \\中继日志信息的记录方式

sync_master_info = 1               \\同步主数据库信息

slave_parallel_workers = 4         \\从服务器的sql线程数,和要复制的数据库相同

binlog_checksum =CRC32             \\二进制日志的校验方式

master_verify_checksum = 1         \\主服务器启用校验

slave_sql_verify_checksum = 1      \\从服务器启用校验

binlog_rows_query_log_events = 1   \\二进制日志详细记录事件

report_port = 3306                 \\提够复制的报告端口,和数据库端口一致

report_host = 10.0.5.151           \\提供复制报告的主机,设为本机地址

mysql> change master to master_host='10.0.5.150',master_user='repuser',

master_password='aixocm',master_auto_position=1;

mysql> start slave;

mysql> show processlist\G

mysql> show status like 'thread%';

slave_skip_errors = all

slave_skip_errors = 1062,1756,2003

3、双主复制

第一台服务器设置

auto_increment_increment = 2  \\数据表记录的自增量,一般等于服务器的数量

auto_increment_offset = 1     \\数据表记录每次的递增量,第一台为1,第二台为2,...

sync_binlog = 0               \\二进制日志写入磁盘的方式

replicate_same_server_id = 0  \\防止mysql循环更新

mysql> grant replication slave on *.* to 'repuser'@'10.0.5.151' identified by 'aixocm';

mysql> flush privileges;

mysql> change master to master_host='10.0.5.151',master_user='repuser',

master_password='aixocm',master_auto_position=1;

mysql> start slave;