大纲
一、主从复制复制原理
二、主从复制的作用及复制类型
三、MySQL5.5异步复制的实现
四、MySQL5.5半同步复制的实现
一、主从复制原理
工作原理
1、当Master接收到了一个写请求，处理写请求，将结果保存至磁盘中，并且会将此操作记录到二进制日志文件中
2、Slave会从Master的二进制日志中读取其中的事件保存至本地的中继日志中
3、Slave会启动一个线程来逐条读取中继日志中的事件并应用于本地的
二、主从复制的作用及复制类型
复制的作用辅助实现备份
提供类似高可用的机制
异地容灾
scale out：分摊负载，如实现读写分离
复制类型
基于语句(statement)复制优点是基于语句的复制的二进制日志可以很好的进行压缩，而且日志的数据量也较小，缺点是基于语句的复制必须是串行化。
基于行复制(Row-Based Replication)，在二进制日志中记录下实际数据的改变,优点就是可以对任何语句都能正确工作，一些语句的效率更高,缺点就是二进制日志会很大，不能使用mysqlbinlog来查看二进制日志.
混合类型(mixed)的复制,默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制.
三、MySQL5.5异步复制的实现
系统环境
CentOS5.8 x86_64
软件包
mysql-5.5.28-linux2.6-x86_64.tar.gz(二进制安装包)
1、Master安装并配置MySQL数据目录底层最好是个逻辑卷，我这里就不再演示逻辑卷的创建了，我的博文中有，此处没有使用逻辑卷
准备数据目录并添加mysql用户
[[email protected] ~]# mkdir -pv /mydata/data
mkdir: created directory `/mydata‘
mkdir: created directory `/mydata/data‘
[[email protected] ~]# groupadd -g 3306 mysql
[[email protected] ~]# useradd -u 3306 -g mysql -M -s /sbin/nologin -d /mydata/data/ mysql
[[email protected] ~]# chown -R mysql.mysql /mydata/data/
解压并初始化mysql
[[email protected] ~]# cd /tmp/
[[email protected] tmp]# tar xf mysql-5.5.28-linux2.6-x86_64.tar.gz -C /usr/local/
[[email protected] tmp]# cd /usr/local/
[[email protected] local]# ln -sv mysql-5.5.28-linux2.6-x86_64 mysql
create symbolic link `mysql‘ to `mysql-5.5.28-linux2.6-x86_64‘
[[email protected] local]# cd mysql
[[email protected] mysql]# chown -R root.mysql ./*
[[email protected] mysql]# ll
total 132
drwxr-xr-x 2 root mysql 4096 Jan 15 15:18 bin
-rw-r--r-- 1 root mysql 17987 Aug 29 2012 COPYING
drwxr-xr-x 4 root mysql 4096 Jan 10 12:01 data
drwxr-xr-x 2 root mysql 4096 Jan 15 15:17 docs
drwxr-xr-x 3 root mysql 4096 Jan 15 15:18 include
-rw-r--r-- 1 root mysql 7604 Aug 29 2012 INSTALL-BINARY
drwxr-xr-x 3 root mysql 4096 Jan 15 15:18 lib
drwxr-xr-x 4 root mysql 4096 Jan 10 12:02 man
drwxr-xr-x 10 root mysql 4096 Jan 15 15:18 mysql-test
-rw-r--r-- 1 root mysql 2552 Aug 29 2012 README
drwxr-xr-x 2 root mysql 4096 Jan 15 15:18 scripts
drwxr-xr-x 27 root mysql 4096 Jan 15 15:18 share
drwxr-xr-x 4 root mysql 4096 Jan 15 15:18 sql-bench
drwxr-xr-x 2 root mysql 4096 Jan 15 15:18 support-files
[[email protected] mysql]# scripts/mysql_install_db --user=mysql --datadir=/mydata/data/
Installing MySQL system tables...
OK
Filling help tables...
OK
To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
./bin/mysqladmin -u root password ‘new-password‘
./bin/mysqladmin -u root -h Master password ‘new-password‘
Alternatively you can run:
./bin/mysql_secure_installation
which will also give you the option of removing the test
databases and anonymous user created by default. This is
strongly recommended for production servers.
See the manual for more instructions.
You can start the MySQL daemon with:
cd . ; ./bin/mysqld_safe &
You can test the MySQL daemon with mysql-test-run.pl
cd ./mysql-test ; perl mysql-test-run.pl
Please report any problems with the ./bin/mysqlbug script!
复制配置文件与服务启动脚本
[[email protected] mysql]# cp support-files/my-large.cnf /etc/my.cnf
[[email protected] mysql]# cp support-files/mysql.server /etc/rc.d/init.d/mysqld
[[email protected] mysql]# chkconfig --add mysqld
修改配置文件增加或修改如下行
[[email protected] mysql]# vim /etc/my.cnf
[mysqld]
datadir = /mydata/data
innodb_file_per_table = 1
log-bin=master-bin
log-bin-index=master-bin.index
增加PATH环境变量
[[email protected] mysql]# echo ‘export PATH=$PATH:/usr/local/mysql/bin‘ > /etc/profile.d/mysql.sh
[[email protected] mysql]# . /etc/profile.d/mysql.sh
启动服务并创建复制用户
[[email protected] mysql]# service mysqld start
Starting MySQL.... [ OK ]
[[email protected] mysql]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.28-log MySQL Community Server (GPL)
Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the buffer.
mysql> GRANT REPLICATION SLAVE ON *.* TO [email protected]%.%‘ IDENTIFIED BY ‘repuser‘;
Query OK, 0 rows affected (0.34 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.02 sec)
mysql> \q
Bye
将mysql的二进制包和配置文件拷贝至从节点
[[email protected] mysql]# scp /tmp/mysql-5.5.28-linux2.6-x86_64.tar.gz Slave:/tmp
mysql-5.5.28-linux2.6-x86_64.tar.gz 100% 178MB 7.4MB/s 00:24
[[email protected] mysql]# scp /etc/my.cnf Slave:/etc/
my.cnf 100% 4745 4.6KB/s 00:00
2、Slave安装并配置MySQL准备数据目录并添加mysql用户
[[email protected] ~]# mkdir -pv /mydata/data
mkdir: created directory `/mydata‘
mkdir: created directory `/mydata/data‘
[[email protected] ~]# groupadd -g 3306 mysql
[[email protected] ~]# useradd -u 3306 -g mysql -M -s /sbin/nologin -d /mydata/data/ mysql
[[email protected] ~]# chown -R mysql.mysql /mydata/data/
解压并初始化mysql
[[email protected] ~]# cd /tmp/
[[email protected] tmp]# tar xf mysql-5.5.28-linux2.6-x86_64.tar.gz -C /usr/local/
[[email protected] tmp]# cd /usr/local/
[[email protected] local]# ln -sv mysql-5.5.28-linux2.6-x86_64 mysql
create symbolic link `mysql‘ to `mysql-5.5.28-linux2.6-x86_64‘
[[email protected] local]# cd mysql
[[email protected] mysql]# chown -R root.mysql ./*
[[email protected] mysql]# ll
total 132
drwxr-xr-x 2 root mysql 4096 Jan 15 15:45 bin
-rw-r--r-- 1 root mysql 17987 Aug 29 2012 COPYING
drwxr-xr-x 4 root mysql 4096 Jan 10 11:15 data
drwxr-xr-x 2 root mysql 4096 Jan 15 15:45 docs
drwxr-xr-x 3 root mysql 4096 Jan 15 15:46 include
-rw-r--r-- 1 root mysql 7604 Aug 29 2012 INSTALL-BINARY
drwxr-xr-x 3 root mysql 4096 Jan 15 15:46 lib
drwxr-xr-x 4 root mysql 4096 Jan 10 11:16 man
drwxr-xr-x 10 root mysql 4096 Jan 15 15:46 mysql-test
-rw-r--r-- 1 root mysql 2552 Aug 29 2012 README
drwxr-xr-x 2 root mysql 4096 Jan 15 15:46 scripts
drwxr-xr-x 27 root mysql 4096 Jan 15 15:46 share
drwxr-xr-x 4 root mysql 4096 Jan 15 15:46 sql-bench
drwxr-xr-x 2 root mysql 4096 Jan 15 15:46 support-files
[[email protected] mysql]# scripts/mysql_install_db --user=mysql --datadir=/mydata/data/
Installing MySQL system tables...
OK
Filling help tables...
OK
To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
./bin/mysqladmin -u root password ‘new-password‘
./bin/mysqladmin -u root -h Slave password ‘new-password‘
Alternatively you can run:
./bin/mysql_secure_installation
which will also give you the option of removing the test
databases and anonymous user created by default. This is
strongly recommended for production servers.
See the manual for more instructions.
You can start the MySQL daemon with:
cd . ; ./bin/mysqld_safe &
You can test the MySQL daemon with mysql-test-run.pl
cd ./mysql-test ; perl mysql-test-run.pl
Please report any problems with the ./bin/mysqlbug script!
复制服务启动脚本
[[email protected] mysql]# cp support-files/mysql.server /etc/rc.d/init.d/mysqld
[[email protected] mysql]# chkconfig --add mysqld
修改配置文件做如下修改
[[email protected] mysql]# vim /etc/my.cnf
[mysqld]
这两行要注释掉，从节点不开启二进制日志功能
#log-bin=Slave-bin
#log-bin-index=Slave-bin.index
修改server_id，不能与主节点重复，要全局唯一
server-id = 11
增加中继日志功能
relay-log = relay-log
relay-log-index = relay-log.index
使从节点只读
read-only = ON
增加PATH环境变量并使之生效
[[email protected] mysql]# echo ‘export PATH=$PATH:/usr/local/mysql/bin‘ > /etc/profile.d/mysql.sh
[[email protected] mysql]# . /etc/profile.d/mysql.sh
启动服务
[[email protected] ~]# service mysqld start
Starting MySQL.... [ OK ]
3、启动Slave上的复制功能首次在Master上查看当前二进制日志所处的position
[[email protected] mysql]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.28-log MySQL Community Server (GPL)
Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the buffer.
mysql> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000001 | 337 | | |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql> SHOW BINLOG EVENTS IN ‘master-bin.000001‘;
+-------------------+-----+-------------+-----------+-------------+----------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+-------------------+-----+-------------+-----------+-------------+----------------------------------------------------------------------------------+
| master-bin.000001 | 4 | Format_desc | 1 | 107 | Server ver: 5.5.28-log, Binlog ver: 4 |
| master-bin.000001 | 107 | Query | 1 | 262 | GRANT REPLICATION SLAVE ON *.* TO [email protected]%.%‘ IDENTIFIED BY ‘repuser‘ |
| master-bin.000001 | 262 | Query | 1 | 337 | FLUSH PRIVILEGES |
+-------------------+-----+-------------+-----------+-------------+----------------------------------------------------------------------------------+
3 rows in set (0.02 sec)
mysql> \q
Bye
再在Slave上启动复制功能
[[email protected] mysql]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.28 MySQL Community Server (GPL)
Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the buffer.
mysql> CHANGE MASTER TO MASTER_HOST=‘172.16.1.101‘,MASTER_PORT=3306,MASTER_USER=‘repuser‘,MASTER_PASSWORD=‘repuser‘,MASTER_LOG_FILE=‘master-bin.000001‘,MASTER_LOG_POS=337;
Query OK, 0 rows affected (0.18 sec)
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 172.16.1.101
Master_User: repuser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 337
Relay_Log_File: relay-log.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: master-bin.000001
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
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: 337
Relay_Log_Space: 107
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: NULL
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: 0
1 row in set (0.00 sec)
mysql> START SLAVE;
Query OK, 0 rows affected (0.49 sec)
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.1.101
Master_User: repuser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 337
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 254
Relay_Master_Log_File: master-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
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: 337
Relay_Log_Space: 404
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: 1
1 row in set (0.04 sec)
4、测试复制功能首先在主节点上创建一个测试库
[[email protected] mysql]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.5.28-log MySQL Community Server (GPL)
Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the buffer.
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.28 sec)
mysql> CREATE DATABASE mydb;
Query OK, 1 row affected (0.10 sec)
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.01 sec)
再在从节点上查看是否有此库
[[email protected] mysql]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.5.28 MySQL Community Server (GPL)
Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the buffer.
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.1.101
Master_User: repuser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 420 # 可以看到已经读取到420位置了
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 337
Relay_Master_Log_File: master-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
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: 420 # 同样执行到了420位置
Relay_Log_Space: 487
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: 1
1 row in set (0.01 sec)
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
可以看到主从复制效果已然实现
查看从节点的错误日志，发现复制相关的日志信息也会记录到错误日志中
[[email protected] mysql]# tail /mydata/data/Slave.err
160115 15:55:06 InnoDB: 1.1.8 started; log sequence number 0
160115 15:55:07 [Note] Server hostname (bind-address): ‘0.0.0.0‘; port: 3306
160115 15:55:07 [Note] - ‘0.0.0.0‘ resolves to ‘0.0.0.0‘;
160115 15:55:07 [Note] Server socket created on IP: ‘0.0.0.0‘.
160115 15:55:08 [Note] Event Scheduler: Loaded 0 events
160115 15:55:08 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: ‘5.5.28‘ socket: ‘/tmp/mysql.sock‘ port: 3306 MySQL Community Server (GPL)
160115 16:05:15 [Note] ‘CHANGE MASTER TO executed‘. Previous state master_host=‘‘, master_port=‘3306‘, master_log_file=‘‘, master_log_pos=‘4‘. New state master_host=‘172.16.1.101‘, master_port=‘3306‘, master_log_file=‘master-bin.000001‘, master_log_pos=‘337‘.
160115 16:10:46 [Note] Slave I/O thread: connected to master [email protected]:3306‘,replication started in log ‘master-bin.000001‘ at position 337
160115 16:10:46 [Note] Slave SQL thread initialized, starting replication in log ‘master-bin.000001‘ at position 337, relay log ‘./relay-log.000001‘ position: 4
四、MySQL5.5半同步复制的实现
原文：http://soysauce93.blog.51cto.com/7589461/1735321
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
