mysql的主从是将主服务器操作记录写入二进制日志文件,然后通过mysql dump线程将日志传到从服务器中继日志中,从服务器在中继日志读取信息并执行.mysql主主架构原理和主从差不多,只是两台服务器都要开启二进制日志文件,并互相传送给对方读取日志中的内容,使数
mysql的主从是将主服务器操作记录写入二进制日志文件,然后通过mysql dump线程将日志传到从服务器中继日志中,从服务器在中继日志读取信息并执行.mysql主主架构原理和主从差不多,只是两台服务器都要开启二进制日志文件,并互相传送给对方读取日志中的内容,使数据同步.两台服务器可以同时读也可以同时写,但不能解决单点故障.
环境规划:
主机名:tong2 ip:192.168.1.248
主机名:tong3 ip:192.168.1.249
数据库:mysql-5.6.21
1.配置服务器网络环境
tong2节点:
[root@tong2 ~]# ifconfig eth0
eth0 link encap:ethernet hwaddr 10:78:d2:c7:17:e8
inet addr:192.168.1.248 bcast:192.168.1.255 mask:255.255.255.0
inet6 addr: fe80::1278:d2ff:fec7:17e8/64 scope:link
up broadcast running multicast mtu:1500 metric:1
rx packets:3118346 errors:0 dropped:0 overruns:0 frame:0
tx packets:16271 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
rx bytes:201242719 (191.9 mib) tx bytes:1754452 (1.6 mib)
[root@tong2 ~]# vim /etc/hosts
192.168.1.248 tong2
192.168.1.249 tong3
[root@tong2 ~]#
tong3节点:
[root@tong3 ~]# ifconfig eth0
eth0 link encap:ethernet hwaddr 10:78:d2:c8:f7:50
inet addr:192.168.1.249 bcast:192.168.1.255 mask:255.255.255.0
inet6 addr: fe80::1278:d2ff:fec8:f750/64 scope:link
up broadcast running multicast mtu:1500 metric:1
rx packets:5055594 errors:0 dropped:0 overruns:0 frame:0
tx packets:96641 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
rx bytes:580382285 (553.4 mib) tx bytes:7055569 (6.7 mib)
[root@tong3 ~]# vim /etc/hosts
192.168.1.248 tong2
192.168.1.249 tong3
[root@tong3 ~]#
2.下载安装mysql-5.6.21软件
tong2节点和tong3节点:
http://mirrors.sohu.com/mysql/mysql-5.6/ --下载关于mysql的软件
[root@tong2 ~]# rpm -ivh mysql-* --安装所有的mysql软件包
3.修改配置文件
tong2节点:
[root@tong2 ~]# vim /usr/my.cnf --在配置文件中添加如下内容
server_id = 10 --服务器的id号
log-bin=mysql-bin --二进制日志文件
log-bin-index=mysql-bin-index --索引文件
relay-log=relay-bin --中继日志文件
relay-log-index=relay-bin-index --中继索引文件
replicate-do-db=tong --要同步的数据库
auto_increment_offset=1 --设置服务器交叉读写数据
auto_increment_increment=2
[root@tong2 ~]#
tong3节点:
[root@tong3 ~]# vim /usr/my.cnf
server_id = 20
log-bin=mysql-bin
log-bin-index=mysql-bin-index
relay-log=relay-bin
relay-log-index=relay-bin-index
replicate-do-db=tong
auto_increment_offset=2
auto_increment_increment=2
[root@tong3 ~]#
3.登陆数据库并创建用户
tong2节点:
[root@tong2 ~]# /etc/init.d/mysql restart --重启服务
shutting down mysql.. success!
starting mysql. success!
[root@tong2 ~]# netstat -antup | grep 3306
tcp 0 0 :::3306 :::* listen 8508/mysqld
[root@tong2 ~]# mysql -u root -p
enter password:
welcome to the mysql monitor. commands end with ; or \g.
your mysql connection id is 5
server version: 5.6.21 mysql community server (gpl)
copyright (c) 2000, 2014, oracle and/or its affiliates. all rights reserved.
oracle is a registered trademark of oracle corporation and/or its
affiliates. other names may be trademarks of their respective
owners.
type 'help;' or '\h' for help. type '\c' to clear the current input statement.
mysql> create database tong; --创建要同步的数据库
query ok, 1 row affected (0.00 sec)
mysql> grant replication slave,replication client on *.* to 'tong2'@'192.168.1.249' identified by 'system'; --创建复制用户
query ok, 0 rows affected (0.00 sec)
mysql> flush tables;
query ok, 0 rows affected (0.00 sec)
mysql>
tong3节点:
[root@tong3 ~]# /etc/init.d/mysql restart
shutting down mysql.. success!
starting mysql. success!
[root@tong3 ~]# netstat -antup | grep 3306
tcp 0 0 :::3306 :::* listen 3484/mysqld
[root@tong3 ~]# mysql -u root -p
enter password:
welcome to the mysql monitor. commands end with ; or \g.
your mysql connection id is 3
server version: 5.6.21 mysql community server (gpl)
copyright (c) 2000, 2014, oracle and/or its affiliates. all rights reserved.
oracle is a registered trademark of oracle corporation and/or its
affiliates. other names may be trademarks of their respective
owners.
type 'help;' or '\h' for help. type '\c' to clear the current input statement.
mysql> create database tong;
query ok, 1 row affected (0.00 sec)
mysql> grant replication slave,replication client on *.* to tong3@'192.168.1.248' identified by 'system';
query ok, 0 rows affected (0.00 sec)
mysql> flush tables;
query ok, 0 rows affected (0.00 sec)
mysql>
4.iptable包过滤
tong2节点和tong3节点:
[root@tong2 ~]# vim /etc/sysconfig/iptables
-a input -m state --state new -m tcp -p tcp -s 192.168.1.249--dport 3306 -j accept --添加规则,放行对mysql的通行
[root@tong2 ~]# /etc/init.d/iptables restart
iptables: setting chains to policy accept: filter [ ok ]
iptables: flushing firewall rules: [ ok ]
iptables: unloading modules: [ ok ]
iptables: applying firewall rules: [ ok ]
[root@tong2 ~]#
5.查看各节点的二进制日志的位置
tong2节点:
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| file | position | binlog_do_db | binlog_ignore_db | executed_gtid_set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 120 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql>
tong3节点:
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| file | position | binlog_do_db | binlog_ignore_db | executed_gtid_set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 | 120 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql>
6.服务器互相连接
tong2节点:
[root@tong2 ~]# mysql -u root -p
enter password:
welcome to the mysql monitor. commands end with ; or \g.
your mysql connection id is 2
server version: 5.6.21-log mysql community server (gpl)
copyright (c) 2000, 2014, oracle and/or its affiliates. all rights reserved.
oracle is a registered trademark of oracle corporation and/or its
affiliates. other names may be trademarks of their respective
owners.
type 'help;' or '\h' for help. type '\c' to clear the current input statement.
mysql> change master to master_host='192.168.1.249',master_user='tong3',master_password='system',master_port=3306,master_log_file='mysql-bin.000004',master_log_pos=120;
query ok, 0 rows affected, 2 warnings (0.43 sec)
mysql> start slave;
query ok, 0 rows affected (0.03 sec)
mysql> show slave status \g
*************************** 1. row ***************************
slave_io_state: waiting for master to send event
master_host: 192.168.1.249
master_user: tong3
master_port: 3306
connect_retry: 60
master_log_file: mysql-bin.000004
read_master_log_pos: 120
relay_log_file: relay-bin.000002
relay_log_pos: 283
relay_master_log_file: mysql-bin.000004
slave_io_running: yes --io线程启动正常
slave_sql_running: yes --sql线程启动正常
replicate_do_db: tong --要同步的数据库
replicate_ignore_db:
replicate_do_table:
replicate_ignore_table:
replicate_wild_do_table:
replicate_wild_ignore_table:
last_errno: 0
last_error:
skip_counter: 0
exec_master_log_pos: 120
relay_log_space: 450
until_condition: none
until_log_file:
until_log_pos: 0
master_ssl_allowed: no
master_ssl_ca_file:
master_ssl_ca_path:
master_ssl_cert:
master_ssl_cipher:
master_ssl_key:
seconds_behind_master: 0
master_ssl_verify_server_cert: no
last_io_errno: 0
last_io_error:
last_sql_errno: 0
last_sql_error:
replicate_ignore_server_ids:
master_server_id: 20
master_uuid: 6009ee25-7f93-11e4-8817-1078d2c8f750
master_info_file: /var/lib/mysql/master.info
sql_delay: 0
sql_remaining_delay: null
slave_sql_running_state: slave has read all relay log; waiting for the slave i/o thread to update it
master_retry_count: 86400
master_bind:
last_io_error_timestamp:
last_sql_error_timestamp:
master_ssl_crl:
master_ssl_crlpath:
retrieved_gtid_set:
executed_gtid_set:
auto_position: 0
1 row in set (0.00 sec)
mysql>
tong3节点:
[root@tong3 ~]# mysql -u root -p
enter password:
welcome to the mysql monitor. commands end with ; or \g.
your mysql connection id is 2
server version: 5.6.21-log mysql community server (gpl)
copyright (c) 2000, 2014, oracle and/or its affiliates. all rights reserved.
oracle is a registered trademark of oracle corporation and/or its
affiliates. other names may be trademarks of their respective
owners.
type 'help;' or '\h' for help. type '\c' to clear the current input statement.
mysql> change master to master_host='192.168.1.248',master_user='tong2',master_password='system',master_port=3306,master_log_file='mysql-bin.000001',master_log_pos=120;
query ok, 0 rows affected, 2 warnings (0.43 sec)
mysql> start slave;
query ok, 0 rows affected (0.06 sec)
mysql> show slave status\g
*************************** 1. row ***************************
slave_io_state: waiting for master to send event
master_host: 192.168.1.248
master_user: tong2
master_port: 3306
connect_retry: 60
master_log_file: mysql-bin.000001
read_master_log_pos: 120
relay_log_file: relay-bin.000002
relay_log_pos: 283
relay_master_log_file: mysql-bin.000001
slave_io_running: yes
slave_sql_running: yes
replicate_do_db: tong
replicate_ignore_db:
replicate_do_table:
replicate_ignore_table:
replicate_wild_do_table:
replicate_wild_ignore_table:
last_errno: 0
last_error:
skip_counter: 0
exec_master_log_pos: 120
relay_log_space: 450
until_condition: none
until_log_file:
until_log_pos: 0
master_ssl_allowed: no
master_ssl_ca_file:
master_ssl_ca_path:
master_ssl_cert:
master_ssl_cipher:
master_ssl_key:
seconds_behind_master: 0
master_ssl_verify_server_cert: no
last_io_errno: 0
last_io_error:
last_sql_errno: 0
last_sql_error:
replicate_ignore_server_ids:
master_server_id: 10
master_uuid: 2cb52da5-759f-11d6-bc85-1078d2c717e8
master_info_file: /var/lib/mysql/master.info
sql_delay: 0
sql_remaining_delay: null
slave_sql_running_state: slave has read all relay log; waiting for the slave i/o thread to update it
master_retry_count: 86400
master_bind:
last_io_error_timestamp:
last_sql_error_timestamp:
master_ssl_crl:
master_ssl_crlpath:
retrieved_gtid_set:
executed_gtid_set:
auto_position: 0
1 row in set (0.00 sec)
mysql>
7.测试服务是否正常
tong2节点:
mysql> create table a (a int);
query ok, 0 rows affected (0.26 sec)
mysql> insert into a values(1),(2),(3),(4);
query ok, 4 rows affected (0.03 sec)
records: 4 duplicates: 0 warnings: 0
mysql> select * from a;
+------+
| a |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
+------+
4 rows in set (0.00 sec)
mysql>
tong3节点:
mysql> \u tong
reading table information for completion of table and column names
you can turn off this feature to get a quicker startup with -a
database changed
mysql> select * from a;
+------+
| a |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
+------+
4 rows in set (0.00 sec)
mysql> create table a1 (a1 int);
query ok, 0 rows affected (0.27 sec)
mysql> insert into a1 values(11),(12),(13),(14);
query ok, 4 rows affected (0.03 sec)
records: 4 duplicates: 0 warnings: 0
mysql> select * from a1;
+------+
| a1 |
+------+
| 11 |
| 12 |
| 13 |
| 14 |
+------+
4 rows in set (0.00 sec)
mysql>
tong2节点:
mysql> select * from a1;
+------+
| a1 |
+------+
| 11 |
| 12 |
| 13 |
| 14 |
+------+
4 rows in set (0.00 sec)
mysql>