一、mysql的主从复制复制过程需要至少两个mysql服务节点，mysql的主从复制框架一般都为一主多从，从多机房汇总到数据中心的模式，而复制过程只能从主服务器到从
一、mysql的主从复制
复制过程需要至少两个mysql服务节点，mysql的主从复制框架一般都为一主多从，，从多机房汇总到数据中心的模式，而复制过程只能从主服务器到从服务器实现。
复制过程中，主服务器负责读/写操作，而从服务器只负责读操作
二、主从复制的功能
1、实现数据冗余、异地灾备恢复、备份
2、实现数据的读/写分离
3、实现数据库服务的负载均衡
4、实现高可用与故障切换
5、实现MySQL的升级测试
三、主从复制过程中注意的事项
1、不要混合shi用不同的存储引擎
2、主从服务器的server-id 要保持不同
3、尽力避免修改从服务器的数据库
4、尽可能的使用基于行或基于混合模式的复制，避免使用基于语句的复制
5、主服务器要启动二进制日志，而从节点则是开启中继日志，而且要确保从服务器的复制线程时刻开启着
6、注意因为磁盘及内存的大小不足而导致的复制崩溃
四、主从复制的具体实现过程
mysql主从复制拓扑图
1、准备两个安装过mysql服务的节点xz：172.16.200.5,node2：172.16.200.7,修改主机名并同步时间；
[root@xz ~]# hostname master
[root@master ~]# crontab -e
*/1 * * * * /usr/sbin/ntpdate 172.16.0.1 &> /dev/null
[root@node2 ~]# hostname slave
[root@slave ~]# crontab -e
*/1 * * * * /usr/sbin/ntpdate 172.16.0.1 &> /dev/null
2、在主节点上创建有复制权限的用户，于从节点上使用授权用户连接测试；
[root@master ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.33-log MySQL Community Server (GPL)
。。。。。
mysql> grant replication slave,replication client on *.* to 'zly'@'172.16.200.7' identified by 'mypass';
Query OK, 0 rows affected (0.35 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.09 sec)
mysql> show grants for 'zly'@'172.16.200.7';
+-----------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for zly@172.16.200.7
|
+-----------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'zly'@'172.16.200.7' IDENTIFIED BY PASSWORD '*6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4' |
+-----------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.03 sec)
...........
[root@slave ~]# mysql -uzly -pmypass -h 172.16.200.5
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.5.33-log MySQL Community Server (GPL)
Copyright (c) 2000, 2013, 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>
3、修改主从两个节点的配置文件，并重新启动mysql服务;
[root@master ~]# vim /etc/my.cnf
log-bin=mysql-bin
log_bin_index = mysql_bin.index
binlog_format=mixed
server-id
= 5
[root@master ~]# killall mysqld
[root@master ~]# ps aux | grep mysqld
root
7700 0.0 0.1 103244 832 pts/3 S+ 09:27 0:00 grep mysqld
[root@master ~]# service mysqld restart
MySQL server PID file could not be found!
[FAILED]
Starting MySQL...
[ OK ]
[root@master ~]# service mysqld restart
Shutting down MySQL.
[ OK ]
Starting MySQL..
[ OK ]
...............
[root@slave ~]# vim /etc/my.cnf
#log-bin=mysql-bin
# binary logging format - mixed recommended
#binlog_format=mixed
skip_slave_start = 1
read_only = 1
relay_log = relay_log
relay_log_index = relay_log.index
# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id
= 7
[root@slave ~]# killall mysqld
[root@slave ~]# ps aux | grep mysqld
root
8796 0.0 0.1 103244 832 pts/4 S+ 23:45 0:00 grep mysqld
[root@slave ~]# service mysqld restart
MySQL server PID file could not be found!
[FAILED]
Starting MySQL..
[ OK ]
[root@slave ~]# service mysqld restart
Shutting down MySQL.
[ OK ]
Starting MySQL..
[ OK ]
4、查看主节点的二进制日志及其事件位置;
mysql> show master status;
+------------------+----------+--------------+------------------+
| File
| Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000011 |
107 |
|
|
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
5、与从节点上复制主节点上的数据;
[root@slave ~]# mysql
mysql> help change master to
Name: 'CHANGE MASTER TO'
Description:
Syntax:
CHANGE MASTER TO option [, option] ...
option:
MASTER_BIND = 'interface_name'
| MASTER_HOST = 'host_name'
| MASTER_USER = 'user_name'
| MASTER_PASSWORD = 'password'
| MASTER_PORT = port_num
| MASTER_CONNECT_RETRY = interval
| MASTER_HEARTBEAT_PERIOD = interval
| MASTER_LOG_FILE = 'master_log_name'
| MASTER_LOG_POS = master_log_pos
| RELAY_LOG_FILE = 'relay_log_name'
| RELAY_LOG_POS = relay_log_pos
| MASTER_SSL = {0|1}
| MASTER_SSL_CA = 'ca_file_name'
| MASTER_SSL_CAPATH = 'ca_directory_name'
| MASTER_SSL_CERT = 'cert_file_name'
| MASTER_SSL_KEY = 'key_file_name'
| MASTER_SSL_CIPHER = 'cipher_list'
| MASTER_SSL_VERIFY_SERVER_CERT = {0|1}
| IGNORE_SERVER_IDS = (server_id_list)
server_id_list:
[server_id [, server_id] ... ]
........................................
mysql> change master to
-> master_host='172.16.200.5',master_user='zly',master_password='mypass
',master_port=3306,master_log_file='mysql-bin.000011',master_log_pos=107;
Query OK, 0 rows affected (0.07 sec)
6、启动从服务器复制线程、查看状态，并查看启动的线程;
95
广告
426
收藏
