首页 > 代码库 > mysql主从复制（添加从服务器）和双主复制
mysql主从复制（添加从服务器）和双主复制
思路：
master:
1、启用二进制日志
2、定义 server-id
3、创建有复制权限的账户
完善设置
4、设置事务日志从内存缓冲直接同步存储到磁盘中
slave：
1、启用中继日志
2、定义server-id
3、使用有复制权限的帐号连接master
4、启动io thread以及sql thread
完善设置
5、定义节点为只读数据库
MariaDB复制操作（从零开始）
环境：
vm1-master:IP 172.16.3.2/16
vm2-slave: IP 172.16.3.10/16
1、mariadb安装(master节点配置)
# mkdir -pv /mydata/data
# tar xf mariadb-5.5.36-linux-x86_64.tar.gz -C /usr/local
# cd /usr/local
# ln -sv mariadb-5.5.36-linux-x86_64 mysql
# useradd -r mysql
# chown -R mysql.mysql /mydata/data/
# cd mysql/
# chown -R root.mysql ./*
# mkdir /etc/mysql
# cp support-files/my-large.cnf /etc/mysql/my.cnf
# vim /etc/mysql/my.cnf
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-external-locking
key_buffer_size = 256M
max_allowed_packet = 1M
table_open_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
# Try number of CPU‘s*2 for thread_concurrency
thread_concurrency = 8
datadir = /mydata/data 添加数据存储目录
sync_binlog=1 设置事务日志从内存缓冲直接同步存储到磁盘中
# cd /usr/local
# cp support-files/mysql.server /etc/rc.d/init.d/mysqld
# chmod +x /etc/rc.d/init.d/mysqld
# chkconfig --add mysqld
# scripts/mysql_install_db --datadir=/mydata/data/ --user=mysql
# mkdir -pv /mydata/binlogs/
# chown -R mysql.mysql /mydata/binlogs
# vim /etc/mysql/my.cnf
log-bin = /mydata/binlogs/master-bin 指定二进制的存放位置，和名称
binlog_format = mixed 开启 记录格式
server-id = 1 server-id 等于1
# service mysqld start
# vim /etc/profile.d/mysql.sh
export PATH=/usr/local/mysql/bin:$PATH
# source /etc/profile.d/mysql.sh
2、创建有复制权限的帐号
# mysql
MariaDB [(none)]> grant replication slave,replication client on *.* to ning@‘172.16.3.10‘identified by ‘ning‘;
Query OK, 0 rows affected (0.04 sec)
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show master status; 查看二进制位置
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000001 | 486 | | |
+-------------------+----------+--------------+------------------+
1 row in set (0.02 sec)
mariadb安装(从slave节点配置)
1、安装
# mkdir -pv /mydata/data
# tar xf mariadb-5.5.36-linux-x86_64.tar.gz -C /usr/local
# cd /usr/local
# ln -sv mariadb-5.5.36-linux-x86_64 mysql
# useradd -r mysql
# chown -R mysql.mysql /mydata/data/
# cd mysql/
# chown -R root.mysql ./*
# mkdir /etc/mysql
# cp support-files/my-large.cnf /etc/mysql/my.cnf
# vim /etc/mysql/my.cnf
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-external-locking
key_buffer_size = 256M
max_allowed_packet = 1M
table_open_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
# Try number of CPU‘s*2 for thread_concurrency
thread_concurrency = 8
datadir = /mydata/data 添加数据存储目录
read_only = 1 启动从节点是只读（不允许写入数据）
# cd /usr/local
# cp support-files/mysql.server /etc/rc.d/init.d/mysqld
# chmod +x /etc/rc.d/init.d/mysqld
# chkconfig --add mysqld
# scripts/mysql_install_db --datadir=/mydata/data/ --user=mysql
# mkdir -pv /mydata/relaylogs/
# chown -R mysql.mysql /mydata/relaylogs
# vim /etc/mysql/my.cnf
#log-bin=mysql-bin 不记录二进制日志
#binlog_format=mixed 不开启日志记录格式
server-id = 11 server-id 和主节点的server-id不同
relay-log = /mydata/relaylogs/relay-log 设置中继日志文件的存放路径
# service mysqld start
# vim /etc/profile.d/mysql.sh
export PATH=/usr/local/mysql/bin:$PATH
# source /etc/profile.d/mysql.sh
2、连接主节点服务器
# mysql
MariaDB [(none)]> change master to master_host=‘172.16.3.2‘, master_user=‘ning‘, master_password=‘ning‘, master_log_file=‘master-bin.000001‘, master_log_pos=486;
Query OK, 0 rows affected (0.05 sec)
# ls /mydata/relaylogs/ 查看下中继日志中是否有从主节点复制过来的二进制日志文件
relay-log.000001 relay-log.index
MariaDB [(none)]> show slave status\G 查看从节点信息
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 172.16.3.2
Master_User: ning
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 486
Relay_Log_File: relay-log.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: master-bin.000001 正在中继的日志文件是什么
Slave_IO_Running: No Slave_IO线程没有启动
Slave_SQL_Running: No Slave_SQL线程没有启动
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: 486
Relay_Log_Space: 245
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)
3、启动线程IO_THREAD和SQL_THREAD
MariaDB [(none)]> start slave IO_THREAD;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> start slave SQL_THREAD;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show slave status\G 查看启动结果
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.3.2
Master_User: ning
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 486
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 530 中继日志的同步过来了
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: 486
Relay_Log_Space: 818
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.00 sec)
主从复制的测试：
我们在主节点上创建个数据看是否能复制过来
1、创建数据：
MariaDB [(none)]> create database ning;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| ning |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.06 sec)
2、从节点上查看数据是否存在
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| ning |----------------------是存在的
| performance_schema |
| test |
+--------------------+
5 rows in set (0.05 sec)
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.3.2
Master_User: ning
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 569 这里可以看到，从主节点复制的二进制日志位置已经到了549
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 613
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: 569
Relay_Log_Space: 901
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.00 sec)
管理命令:
1、停止从主节点复制
MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.14 sec)
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 172.16.3.2
Master_User: ning
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 569
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 613
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: 569
Relay_Log_Space: 901
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: 1
1 row in set (0.00 sec)
2、启动从主节点复制
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.3.2
Master_User: ning
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 569
Relay_Log_File: relay-log.000003
Relay_Log_Pos: 530
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: 569
Relay_Log_Space: 1186
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.00 sec)
=====================================================================================================================
MariaDB复制操作（不是从零开始添加从节点）
环境：
和上面的基础环境一致
思路：
master:
执行完全备份
slave：
1、导入完全备份
2、连接master，并指定的从完全备份的结束日志位置开始，复制二进制日志到，中继日志中。
3、开启从节点
master节点配置
1、查看下现在的主节二进制日志位置
MariaDB [ning]> show master status;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000003 | 8096 | | |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
2、完整备份
# vim /etc/profile.d/mysqldump.sh 由于是编译安装，mysqldump需要设置路径
export PATH=/usr/local/mysql/bin:$PATH
# source /etc/profile.d/mysqldump.sh
# mysqldump --all-databases --lock-all-tables --master-data=http://www.mamicode.com/2 > all.sql 完全备份整个数据库
# cat all.sql
-- CHANGE MASTER TO MASTER_LOG_FILE=‘master-bin.000003‘, MASTER_LOG_POS=8096; 查看完全备份的二进制日志的结束位置；
3、传送完整备份到从节点
# scp all.sql 172.16.3.10:/root/
slave从节点配置
1、把完全备份导入到数据库中
# mysql < all.sql
2、启动连接主服务器
MariaDB [(none)]> change master to master_host=‘172.16.3.2‘, master_user=‘ning‘, master_password=‘ning‘ , master_log_file=‘master-bin.000003‘, master_log_pos=8096;
Query OK, 0 rows affected (0.03 sec)
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 172.16.3.2
Master_User: ning
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000003
Read_Master_Log_Pos: 8096 查看是否和完全备份的二进制日志位置号相同（如果相同就可以启动从节点了）
Relay_Log_File: relay-log.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: master-bin.000003
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: 8096
Relay_Log_Space: 245
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)
3、开启从节点（如果没有安装两线程，需要安装在启动；安装上面实例中有）
MariaDB [(none)]> start slave; 启动从节点从主节点复制
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.3.2
Master_User: ning
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000003
Read_Master_Log_Pos: 8337
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 771
Relay_Master_Log_File: master-bin.000003
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: 8337
Relay_Log_Space: 1059
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.00 sec)
====================================================================================
环境：
vm1-master:IP 172.16.3.2/16
vm2-slave: IP 172.16.3.10/16
时间必须同步：
思路：
双主模型
1、双主节点上都得创建具有复制权限的用户
2、双节点都得启动中继日志和二进制日志
3、为保证具有自动增长功能的字段能正确生成ID，需要配置两个节点分别使用偶数或奇数ID号
4、都需要配置为自己的主节点
master1主配置
1、
# mkdir /mydata/{data binlogs relaylogs}
2、配置文件
# vim /etc/mysql/my.cnf
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-external-locking
key_buffer_size = 256M
max_allowed_packet = 1M
table_open_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
thread_concurrency = 8
datadir = /mydata/data 指定数据目录
#tmpdir = /tmp/
log-bin=/mydata/binlogs/master1-bin 指定二进制文件的存储位置
relay-log=/mydata/relaylogs/relay-log 指定中继日志的存放位置
binlog_format=mixed 指定记录二进制日志的格式
server-id = 1 serverID
auto-increment-offset = 1 开始ID为1
auto-increment-increment = 2 每次偏移2
# service mysqld start
3、创建具有复制权限的用户
# mysql
MariaDB [(none)]> grant replication slave,replication client on *.* to ning@‘172.16.3.10‘identified by ‘ning‘;
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
4、查看当前二进制日志的位置
MariaDB [(none)]> show master status;
+--------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| master1-bin.000001 | 496 | | |
+--------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
5、连接master1节点进行复制
MariaDB [(none)]> change master to master_host=‘172.16.3.10‘, master_user=‘ning‘, master_password=‘ning‘, master_log_file=‘master2-bin.000001‘, master_log_pos=495;
Query OK, 0 rows affected (0.13 sec)
master2主配置文件
1、
# mkdir /mydata/{data binlogs relaylogs}
2、主配置文件
# vim /etc/mysql/my.cnf
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-external-locking
key_buffer_size = 256M
max_allowed_packet = 1M
table_open_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
thread_concurrency = 8
datadir = /mydata/data 指定数据目录
#tmpdir = /tmp/
log-bin=/mydata/binlogs/master2-bin 指定二进制文件的存储位置
relay-log=/mydata/relaylogs/relay-log 指定中继日志的存放位置
binlog_format=mixed 指定记录二进制日志的格式
server-id = 11 serverID
auto-increment-offset = 2 开始ID为2
auto-increment-increment = 2 每次偏移2
# service mysqld start
3、创建具有复制权限的用户
# mysql
MariaDB [(none)]> grant replication slave,replication client on *.* to ning@‘172.16.3.2‘identified by ‘ning‘;
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
4、查看当前二进制日志的位置
MariaDB [(none)]> show master status;
+--------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| master2-bin.000001 | 495 | | |
+--------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
5、连接master1节点进行复制
MariaDB [(none)]> change master to master_host=‘172.16.3.2‘, master_user=‘ning‘, master_password=‘ning‘, master_log_file=‘master1-bin.000001‘, master_log_pos=496;
Query OK, 0 rows affected (0.13 sec)
测试：双方服务器都启动服务start slave
双方创建数据，查看是否都能看到对方的数据
mysql主从复制（添加从服务器）和双主复制