MySQL异步模式_MySQL主从复制（异步模式）

MySQL主从复制有异步模式、半同步模式、GTID模式以及多源复制模式，MySQL默认模式是异步模式。所谓异步模式，只MySQL 主服务器上I/O thread 线程将二进制日志写入binlog文件之后就返回客户端结果，不会考虑二进制日志是否完整传输到从服务器以及是否完整存放到从服务器上的relay日志中，这种模式一旦主服务(器)宕机，数据就会发生丢失。

环境：

1 [[email protected] ~]# cat /etc/redhat-release2 CentOS Linux release 7.2.1511(Core)3 [[email protected] ~]# uname -a4 Linux localhost.localdomain 3.10.0-327.el7.x86_64 #1 SMP Thu Nov 19 22:10:57 UTC 2015 x86_64 x86_64 x86_64 GNU/Linux5 [[email protected] ~]#

View Code

MySQL版本：

mysql-5.7.22-linux-glibc2.12-x86_64.tar

主从复制IP规划：

主：192.168.112.2220

从：192.168.112.221

部署步骤：

tar xf mysql-5.7.22-linux-glibc2.12-x86_64.tar.gzmkdir -p /applicationmv mysql-5.7.22-linux-glibc2.12-x86_64 /application/

ln -s /application/mysql-5.7.22-linux-glibc2.12-x86_64 /application/mysql

groupadd mysql

useradd mysql-g mysql -s /sbin/nologinmkdir -p /application/datachown -R mysql.mysql /application/mysqlchown -R mysql.mysql /application/mysql/*chown -R mysql.mysql /application/data

安装常用依赖库：

[[email protected] ~]# yum install cmake gcc gcc-c++ libaio-devel automake autoconf bison libtool ncurses-devel libgcrypt-devel libev-devel perl-Digest-MD5 perl-DBI perl-DBD-MySQL

编写配置文件my.cnf：

1 [client]2 port = 3306

3 socket = /tmp/mysql.sock4

5 [mysql]6 prompt=""

7 no-auto-rehash8

9 [mysqld]10 user =mysql11 port = 3306

12 basedir = /application/mysql13 datadir = /application/data/mysql/

14 socket = /tmp/mysql.sock15 character-set-server =utf8mb416 skip_name_resolve = 1

17 open_files_limit = 65535

18 back_log = 1024

19 max_connections = 512

20 max_connect_errors = 100000

21 table_open_cache = 1024

22 table_definition_cache = 1024

23 thread_stack =512K24 external-locking =FALSE25 max_allowed_packet =32M26 sort_buffer_size =4M27 join_buffer_size =4M28 thread_cache_size = 0

29 query_cache_size = 0

30 query_cache_type = 0

31 interactive_timeout = 600

32 wait_timeout = 600

33 tmp_table_size =32M34 slow_query_log =1

35 slow_query_log_file = /application/data/mysql/slow.log36 log-error = /application/data/mysql/error.log37 long_query_time = 0.5

38 server-id = 33060220

39 log-bin = /application/data/mysql/mysql-binlog40 sync_binlog = 1

41 binlog_cache_size =4M42 max_binlog_cache_size =1G43 max_binlog_size =1G44 expire_logs_days = 7

45 master_info_repository =TABLE46 relay_log_info_repository =TABLE47 #gtid_mode =on48 enforce_gtid_consistency = 1

49 log_slave_updates50 binlog_format =row51 relay_log_recovery = 1

52 relay-log-purge = 1

53 key_buffer_size =32M54 read_buffer_size =8M55 read_rnd_buffer_size =4M56 bulk_insert_buffer_size =64M57

58 lock_wait_timeout = 3600

59 explicit_defaults_for_timestamp = 1

60 innodb_thread_concurrency = 0

61 innodb_sync_spin_loops = 100

62 innodb_spin_wait_delay = 30

63 transaction_isolation = REPEATABLE-READ64 innodb_buffer_pool_size =1024M65 innodb_buffer_pool_instances = 8

66 innodb_buffer_pool_load_at_startup = 1

67 innodb_buffer_pool_dump_at_shutdown = 1

68 innodb_data_file_path =ibdata1:1G:autoextend69 innodb_flush_log_at_trx_commit = 1

70 innodb_log_buffer_size =32M71 innodb_log_file_size =2G72 innodb_log_files_in_group = 2

73 innodb_io_capacity_max = 2000

74 innodb_io_capacity_max = 4000

75 innodb_flush_neighbors = 0

76 innodb_write_io_threads = 8

77 innodb_read_io_threads = 8

78 innodb_purge_threads = 4

79 innodb_page_cleaners = 4

80 innodb_open_files = 65535

81 innodb_max_dirty_pages_pct = 50

82 innodb_flush_method =O_DIRECT83 innodb_lru_scan_depth = 4000

84 innodb_checksum_algorithm =crc3285 innodb_lock_wait_timeout = 10

86 innodb_rollback_on_timeout = 1

87 innodb_print_all_deadlocks = 1

88 innodb_file_per_table = 1

89 innodb_online_alter_log_max_size =4G90 internal_tmp_disk_storage_engine =InnoDB91 innodb_stats_on_metadata = 0

92 innodb_status_file = 1

93 innodb_status_output = 0

94 innodb_status_output_locks = 0

95

96 #performance_schema97

98 performance_schema = 1

99 performance_schema_instrument = ‘%=on‘

100

101 #innodb monitor102 innodb_monitor_enable="module_innodb"

103 innodb_monitor_enable="module_server"

104 innodb_monitor_enable="module_dml"

105 innodb_monitor_enable="module_ddl"

106 innodb_monitor_enable="module_trx"

107 innodb_monitor_enable="module_os"

108 innodb_monitor_enable="module_purge"

109 innodb_monitor_enable="module_log"

110 innodb_monitor_enable="module_lock"

111 innodb_monitor_enable="module_buffer"

112 innodb_monitor_enable="module_index"

113 innodb_monitor_enable="module_ibuf_system"

114 innodb_monitor_enable="module_buffer_page"

115 innodb_monitor_enable="module_adaptive_hash"

116

117 [mysqldump]118 quick119 max_allowed_packet = 32M

初始化数据操作：

[[email protected] ~]# mysqld --defaults-file=/etc/my.cnf --basedir=/application/mysql --datadir=/application/data/ --user=mysql --initialize

启动数据库：

[[email protected] ~]# /application/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql &

创建用于主从复制的用户及账户：

[email protected] 14:47: [(none)]> create user ‘rep‘@‘192.168.112.%‘ identified by ‘rep123‘;

Query OK,0 rows affected (0.0814:51: [(none)]> grant replication slave on *.* to ‘rep‘@‘192.168.112.%‘;

Query OK,0 rows affected (0.01sec)

[email protected] 14:52:  [(none)]> flush privileges;

Query OK, 0 rows affected (0.03 sec)

[email protected]14:52: [(none)]>

导出数据库：

mysqldump --single-transaction -uroot -hlocalhost [email protected]123 --master-data=2 -A >all.sql

[[email protected] ~]# netstat -lnupt|grep 3306

tcp6       0      0 :::3306                 :::*                    LISTEN      5819/mysqld

[[email protected] ~]#

说明：--single-transaction  如果备份的数据库存储引擎是InnoDB，该参数可以保证数据的一致性，配合RR隔离级别一起使用，当发起事务时，读取一个数据的快照直到备份结束时，都不会读取到本事务开始之后提交的任何数据，--master-data=2，让备份出来的文件中记录备份这一时刻的binlog文件与position号，即记录备份时的binlog文件名和偏移位置

至此，主从复制在主服务器上操作完成，后面在从服务器上操作：

同样部署MySQL服务，然后登陆MySQL，导入all.sql文件，再运行：

CHANGE MASTER TO

MASTER_HOST=‘192.168.112.220‘,

MASTER_USER=‘rep‘,

MASTER_PASSWORD=‘rep123‘,

MASTER_PORT=3306,

MASTER_LOG_FILE=‘mysql-binlog.000003‘,

MASTER_LOG_POS=863;

开启主从复制状态：

[email protected] 14:55: [(none)]> start slave;

查看主从复制状态：

1 [email protected] 14:56: [(none)]>show slave status\G;2 *************************** 1. row ***************************

3 Slave_IO_State: Waiting formaster to send event4 Master_Host: 192.168.112.220

5 Master_User: bak6 Master_Port: 3306

7 Connect_Retry: 60

8 Master_Log_File: mysql-binlog.000003

9 Read_Master_Log_Pos: 2274

10 Relay_Log_File: localhost-relay-bin.000002

11 Relay_Log_Pos: 1734

12 Relay_Master_Log_File: mysql-binlog.000003

13 Slave_IO_Running: Yes14 Slave_SQL_Running: Yes15 Replicate_Do_DB:16 Replicate_Ignore_DB:17 Replicate_Do_Table:18 Replicate_Ignore_Table:19 Replicate_Wild_Do_Table:20 Replicate_Wild_Ignore_Table:21 Last_Errno: 0

22 Last_Error:23 Skip_Counter: 0

24 Exec_Master_Log_Pos: 2274

25 Relay_Log_Space: 1945

26 Until_Condition: None27 Until_Log_File:28 Until_Log_Pos: 0

29 Master_SSL_Allowed: No30 Master_SSL_CA_File:31 Master_SSL_CA_Path:32 Master_SSL_Cert:33 Master_SSL_Cipher:34 Master_SSL_Key:35 Seconds_Behind_Master: 0

36 Master_SSL_Verify_Server_Cert: No37 Last_IO_Errno: 0

38 Last_IO_Error:39 Last_SQL_Errno: 0

40 Last_SQL_Error:41 Replicate_Ignore_Server_Ids:42 Master_Server_Id: 33060220

43 Master_UUID: cda19536-5749-11e8-8c7a-000c29268dcd44 Master_Info_File: mysql.slave_master_info45 SQL_Delay: 0

46 SQL_Remaining_Delay: NULL47 Slave_SQL_Running_State: Slave has read all relay log; waiting for moreupdates48 Master_Retry_Count: 86400

49 Master_Bind:50 Last_IO_Error_Timestamp:51 Last_SQL_Error_Timestamp:52 Master_SSL_Crl:53 Master_SSL_Crlpath:54 Retrieved_Gtid_Set:55 Executed_Gtid_Set:56 Auto_Position: 0

57 Replicate_Rewrite_DB:58 Channel_Name:59 Master_TLS_Version:60 1 row in set (0.00sec)61

62 ERROR:63 No query specified64

65 [email protected] 14:56: [(none)]>

View Code

至此主从复制搭建完成

原文：https://www.cnblogs.com/kindnull/p/9040968.html