MySQL主从复制有异步模式、半同步模式、GTID模式以及多源复制模式,MySQL默认模式是异步模式。所谓异步模式,是指MySQL主服务器上I/O thread 线程将二进制日志写入binlog文件之后就返回客户端结果,不会考虑二进制日志是否完整传输到从服务器以及是否完整存放到从服务器上的relay日志中,这种模式一旦主服务(器)宕机,数据就会发生丢失。
环境:
1 [root@localhost ~]# cat /etc/redhat-release2 CentOS Linux release 7.2.1511(Core)3 [root@localhost ~]# 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 [root@localhost ~]#
View Code
MySQL版本:
mysql-5.7.22-linux-glibc2.12-x86_64.tar
主从复制IP规划:
主:192.168.112.220
从: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
安装常用依赖库:
[root@localhost ~]# 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="\u@db \R:\m:\s [\d]>"
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
初始化数据操作:
[root@localhost ~]# mysqld --defaults-file=/etc/my.cnf --basedir=/application/mysql --datadir=/application/data/ --user=mysql --initialize
启动数据库:
[root@localhost ~]# /application/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql &
[root@localhost ~]# netstat -lnupt|grep 3306
tcp6 0 0 :::3306 :::* LISTEN 5819/mysqld
[root@localhost ~]#
创建用于主从复制的用户及账户:
root@db 14:47: [(none)]> create user 'rep'@'192.168.112.%' identified by 'rep123';
Query OK,0 rows affected (0.08sec)
root@db14:51: [(none)]> grant replication slave on *.* to 'rep'@'192.168.112.%';
Query OK,0 rows affected (0.01sec)
root@db 14:52: [(none)]> flush privileges;
Query OK, 0 rows affected (0.03 sec)
root@db14:52: [(none)]>
导出数据库:
mysqldump --single-transaction -uroot -hlocalhost -proot@123 --master-data=2 -A >all.sql
说明:--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;
开启主从复制状态:
root@db 14:55: [(none)]> start slave;
查看主从复制状态:
1 root@db 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: rep6 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 root@db 14:56: [(none)]>
View Code
从上述状态看到I/O threads和SQL threads都处于OK,说明主从复制已经配置完成。
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
至此主从复制搭建完成