zhmg23

我们是如此的不同
资深运维工程师互荐群: 102123162

配置MySQL5.6主从同步(非GTID方式)

说明:

 A主机: 192.168.5.44  (主)

 B主机: 192.168.5.45  (从)

 操作系统版本: CentOS6.5_x64

 数据库版本:   MySQL 5.6.23

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

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

(3)  slave重做中继日志中的事件,将改变反映它自己的数据。

下图描述了复制的过程:

配置MySQL5.6主从同步(非GTID方式) - zhm - 合肥运维


 此次配置,是在主库已经在运行的情况下,新增了一台从库,安装配置要求与主库一致的情况下进行配置的!初次进行主从配置,需要中断业务约20分钟!

 

1、主数据库上创建用于主从复制的账户

主库:

Mysql> grant replication slave on *.* TO Repl@"192.168.5.45" identified by 'Replpasswd';

Mysql> flush privileges;

 

注:以上IP地址为从数据库IP地址

 

2、把主数据库锁表

(禁止再插入数据以获取主数据库的的二进制日志坐标)

mysql> flush tables with read lock;

Query OK, 0 rows affected (0.02 sec)

 

3、查看并记录主数据库的状态

记录下File字段和Position字段的值,在配置从服务器时有用到

mysql> show master status;

+------------------------+----------+--------------+------------------+-------------------+

| File                   | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------------+----------+--------------+------------------+-------------------+

| mysqlmaster-bin.000001 |      413 |              |                  |                   |

+------------------------+----------+--------------+------------------+-------------------+

1 row in set (0.01 sec)

 配置MySQL5.6主从同步(非GTID方式) - zhm - 合肥运维

 

 

4、备份主数据库要同步的数据

mysqldump -uroot -pClientPasswd -h127.0.0.1 -P3306 database > /tmp/ database.sql

 

 

5、把主数据库备份的数据导入从数据库

在从数据库服务器上导入刚刚需要同步的数据库文件

mysql -uroot -pClientPasswd -h127.0.0.1 -P3306 < /tmp/ database.sql

 

6、从数据库上设置同步主数据库

mysql> change master to master_host = '192.168.5.44',master_user='Repl',master_password='Replpasswd',master_log_file='mysqlmaster-bin.000001',master_log_pos=413;

Query OK, 0 rows affected, 2 warnings (0.49 sec)

mysql> start slave;

 

7、主库解锁

mysql> unlock tables;

 

8、查询从数据库的复制线程状态

mysql> show slave status \G

 配置MySQL5.6主从同步(非GTID方式) - zhm - 合肥运维

 

如果Slave_IO_Running: Yes

        Slav_SQL_Running: Yes

说明主从同步成功! 

9、主从数据库配置文件

主数据库配置

[client]

socket = /var/lib/mysql/mysql.sock

port = 3306

[mysqld]

port = 3306

socket = /var/lib/mysql/mysql.sock

 

basedir = /usr/local/mysql

datadir = /data/mysql

pid-file = /data/mysql/HAproxy1.pid

user = mysql

server_id = 44

replicate_wild_ignore_table=mysql.%

replicate_wild_ignore_table=information_schema.%

replicate_wild_ignore_table=performance_schema.%

log-bin=mysqlmaster-bin.log

innodb_flush_log_at_trx_commit=1

log_bin_trust_function_creators=1

default_storage_engine = InnoDB

 

# InnoDB

innodb_buffer_pool_size = 128M

#innodb_log_file_size = 48M

innodb_file_per_table = 1

#innodb_flush_method = O_DIRECT

 

# MyISAM

#key_buffer_size = 48M

 

# character-set

character-set-server=utf8

collation-server=utf8_general_ci

 

# name-resolve

skip-host-cache

skip-name-resolve

max_connections = 600

max_connect_errors = 30

open_files_limit = 65535

table_open_cache =         2000

max_allowed_packet = 4M

binlog_cache_size = 4M

max_heap_table_size = 16M

tmp_table_size = 16M

#lower_case_table_names = 1

#skip-external-locking                      

# LOG

log_error = /data/mysql/HAproxy1.log

long_query_time = 2

slow-query-log

slow_query_log_file = /data/mysql/HAproxy1-slow.log

 

# Others

explicit_defaults_for_timestamp=true

open_files_limit = 5000

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

       

[mysqldump]

quick

max_allowed_packet=16M

 

从配置文件:

[client]

socket = /var/lib/mysql/mysql.sock

port = 3306

 

[mysqld]

port = 3306

socket = /var/lib/mysql/mysql.sock

 

basedir = /usr/local/mysql

datadir = /data/mysql

pid-file = /data/mysql/HAproxy2.pid

user = mysql

server_id = 45

log-bin=mysqlslave-bin.log

relay-log-index=slave-relay-bin.index

relay-log=slave-relay-bin

log_bin_trust_function_creators=1

innodb_flush_log_at_trx_commit=1

default_storage_engine = InnoDB

# InnoDB

innodb_buffer_pool_size = 128M

#innodb_log_file_size = 48M

innodb_file_per_table = 1

#innodb_flush_method = O_DIRECT

 

# MyISAM

#key_buffer_size = 48M

 

# character-set

character-set-server=utf8

collation-server=utf8_general_ci

# name-resolve

skip-host-cache

skip-name-resolve

max_connections = 600

max_connect_errors = 30

open_files_limit = 65535

table_open_cache =         2000

max_allowed_packet = 4M

binlog_cache_size = 4M

max_heap_table_size = 16M

tmp_table_size = 16M

#lower_case_table_names = 1 

#skip-external-locking                      

 

# LOG

log_error = /data/mysql/HAproxy2.log

long_query_time = 2

slow-query-log

slow_query_log_file = /data/mysql/HAproxy2-slow.log

# Others

explicit_defaults_for_timestamp=true

open_files_limit = 5000

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES     

[mysqldump]

quick

max_allowed_packet=16M

 

10、日常维护命令及注意事项

1.  停止MYSQL同步

mysql> STOP SLAVE IO_THREAD;    #停止IO进程

mysql> STOP SLAVE SQL_THREAD;    #停止SQL进程

mysql> STOP SLAVE;     #停止IO和SQL进程


2.  启动MYSQL同步

mysql> START SLAVE IO_THREAD;    #启动IO进程

mysql> START SLAVE SQL_THREAD;   #启动SQL进程

mysql> START SLAVE;              #启动IO和SQL进程


3. 重置MYSQL同步

mysql> RESET SLAVE;

用于让从属服务器忘记其在主服务器的二进制日志中的复制位置, 它会删除master.info和relay-log.info文件,以及所有的中继日志,并启动一个新的中继日志,当你不需要主从的时候可以在从上执行这个操作。

不然以后还会同步,可能会覆盖掉你的数据库!

4.临时跳过MYSQL同步错误

mysql主从同步经常遇到错误,比如一个主键冲突等,那么我就需要在确保那一行数据一致的情况下临时的跳过这个错误,那就需要使用SQL_SLAVE_SKIP_COUNTER = n命令了,n是表示跳过后面的n个事件,比如我跳过一个事件的操作如下:

mysql> STOP SLAVE;

mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;

mysql> START SLAVE;


5.从指定位置重新同步

有的时候主从同步有问题了以后,需要从log位置的下一个位置进行同步,相当于跳过那个错误,这时候也可以使用CHANGE MASTER命令来处理,只要找到对应的LOG位置就可以,比如:

mysql> CHANGE MASTER TO MASTER_HOST='192.168.5.44',MASTER_USER='repl',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=106;

mysql> START SLAVE;


6.查看MYSQL同步状态

mysql> SHOW SLAVE STATUS; #查看从状态

mysql> SHOW Master STATUS\G #查看主状态

SHOW SLAVE STATUS;这个命令主要查看Slave_IO_Running、Slave_SQL_Running、Seconds_Behind_Master、Last_IO_Error、Last_SQL_Error这些值来把握复制的状态。


7.几点主从维护建议

不要乱使用SQL_SLAVE_SKIP_COUNTER命令

可以结合percona-toolkit工具pt-table-checksum定期查看数据是否一致

使用replicate-wild-ignore-table选项而不要使用replicate-do-db或者replicate-ignore-db

将主服务器的日志模式调整成mixed

每个表都加上主键,主键对数据库的同步会有影响尤其是居于ROW复制模式

 

评论