MySQL主从
这里记录一下MySQL的主从复制
我现在两台测试服务器的MySQL版本都是5.6.23
创建复制帐号1、在Master的数据库中建立一个备份帐户：每个slave使用标准的MySQL用户名和密码连接master。进行复制操作的用户会授予REPLICATION SLAVE权限。用户名的密码都会存储在文本文件master.info中
mysql> grant replication slave,reload,super on *.* to repl@'10.230.200.20' identified by 'kongzhong789' with grant option;
Query OK, 0 rows affected (0.00 sec)
建立一个帐户repl，并且只能允许从10.230.200.20这个地址上来登陆，密码是kongzhong789。这里@后面的ip地址就是slave的ip
MySQL主从是基于binlog日志，所以在安装好数据库后就要开启binlog。这样好处是，一方面可以用binlog恢复数据库，另一方面可以为主从做准备。
my.cnf配置文件添加如下配置（打开二进制日志，并设置唯一的serverID号）
log_bin = /data/dbdata/3306/bin-log/mysql-bin server-id = 19
重启数据库，使得配置生效
[root@bao-work-1 mysql56]# /etc/init.d/mysql_3306 restart
拷贝数据（假如是你完全新安装mysql主从服务器，这个一步就不需要。因为新安装的master和slave有相同的数据）
这里我们全库备份一下，因为我们的主库是有数据的，所以这一步必不可少
[root@bao-work-1 ~]# mysqldump -uroot -pkongzhong123 --routines --single_transaction --master-data=2 --all-databases >alldump.sql
参数说明：
--routines：导出存储过程和函数
--single_transaction：导出开始时设置事务隔离状态，并使用一致性快照开始事务，然后unlock tables;而lock-tables是锁住一张表不能写操作，直到dump完毕。
--master-data：默认等于1，将dump起始（change master to）binlog点和pos值写到结果中，等于2是将change master to写到结果中并注释。
现在我们要修改一下从库的配置文件
添加如下配置
log_bin = /data/dbdata/3306/bin-log/mysql-bin server-id = 20 relay_log = mysql-relay-bin log_slave_updates = 1 read_only = 1
server_id 是必须的，而且唯一。slave没有必要开启二进制日志，但是在一些情况下，必须设置，例如，如果slave为其它slave的master，必须设置 bin_log。在这里，我们开启了二进制日志，而且显示的命名(默认名称为hostname，但是，如果hostname改变则会出现问题)。
relay_log配置中继日志，log_slave_updates表示slave将复制事件写进自己的二进制日志(后面会看到它的用处)，这样话，从库从主库同步数据，只要是数据变化的日志都会写到二进制日志中。这样我们后面要说的备份数据库中可以用到，这样的话，我就可以备份从库了，同时从库的二进制日志也可以用上了。
有 些人开启了slave的二进制日志，却没有设置log_slave_updates，然后查看slave的数据是否改变，这是一种错误的配置。所以，尽量 使用read_only，它防止改变数据(除了特殊的线程)。但是，read_only并是很实用，特别是那些需要在slave上创建表的应用。
重启数据库
[root@bao-work-2 3306]# /etc/init.d/mysql_3306 restart
接下来要做的就是把备份库拷贝到从库
[root@bao-work-1 ~]# scp alldump.sql root@10.230.200.20:/root
导入数据
[root@bao-work-2 ~]# mysql -uroot -p -S /data/dbdata/3306/mysql.sock < alldump.sql
在备份文件中查看binlog的pos值
[root@bao-work-2 ~]# head -50 alldump.sql
-- MySQL dump 10.13 Distrib 5.6.23, for Linux (x86_64)
--
-- Host: localhost Database:
-- ------------------------------------------------------
-- Server version 5.6.23-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Position to start replication or point-in-time recovery from
--
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=120;
--
-- Current Database: `huamu_licai`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `huamu_licai` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `huamu_licai`;
--
-- Table structure for table `act_t_activity`
--
DROP TABLE IF EXISTS `act_t_activity`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `act_t_activity` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`create_date` date DEFAULT NULL,
`mobile` char(30) DEFAULT NULL,
`account` char(150) DEFAULT NULL,
`optionv` char(30) DEFAULT NULL,
`ip` char(200) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
从上面可以看到pos值是120，所以这里我们就从120这个日志点开始同步
因为这两台服务器都是我的测试服务器，我要是不手动修改数据的话，数据是不会变化的
现在两台服务器的数据已经是一样的了，现在为了试验，在主库上进行如下操作
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000006 | 120 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
这里我们看到和备份文件中的pos值是一样的
现在为了试验我改一下主库的数据
mysql> select * from t_service;
+----+---------+--------------------+----------+-------------+---------------+-------------+---------------+-------------+---------------+
| id | area_id | service | is_alive | create_time | create_person | update_time | update_person | delete_time | delete_person |
+----+---------+--------------------+----------+-------------+---------------+-------------+---------------+-------------+---------------+
| 1 | 1 | ssssss反反复复 | 0 | 2016-12-09 | NULL | 2016-12-09 | NULL | NULL | NULL |
| 2 | 1 | 呵呵哈哈哈 | 0 | 2016-12-09 | | NULL | NULL | NULL | NULL |
| 3 | 5 | 君临天下 | 1 | 2016-12-09 | | NULL | NULL | NULL | NULL |
| 4 | 5 | 王者归来 | 1 | 2016-12-09 | | NULL | NULL | NULL | NULL |
+----+---------+--------------------+----------+-------------+---------------+-------------+---------------+-------------+---------------+
4 rows in set (0.00 sec)
mysql> delete from t_service where area_id=1;
Query OK, 2 rows affected (0.04 sec)
mysql> select * from t_service;
+----+---------+--------------+----------+-------------+---------------+-------------+---------------+-------------+---------------+
| id | area_id | service | is_alive | create_time | create_person | update_time | update_person | delete_time | delete_person |
+----+---------+--------------+----------+-------------+---------------+-------------+---------------+-------------+---------------+
| 3 | 5 | 君临天下 | 1 | 2016-12-09 | | NULL | NULL | NULL | NULL |
| 4 | 5 | 王者归来 | 1 | 2016-12-09 | | NULL | NULL | NULL | NULL |
+----+---------+--------------+----------+-------------+---------------+-------------+---------------+-------------+---------------+
2 rows in set (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000006 | 369 | | | |
+------------------+----------+--------------+------------------+-------------------+
现在主库比从库少两行数据了
mysql> change master to master_host='10.230.200.19', -> master_user='repl', -> master_password='kongzhong789', -> master_log_file='mysql-bin.000006', -> master_log_pos=120; Query OK, 0 rows affected, 2 warnings (0.23 sec) mysql> start slave; Query OK, 0 rows affected (0.02 sec) mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Connecting to master Master_Host: 10.230.200.19 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000006 Read_Master_Log_Pos: 120 Relay_Log_File: mysql-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: mysql-bin.000006 Slave_IO_Running: Connecting 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: 120 Relay_Log_Space: 120 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: 2003 Last_IO_Error: error connecting to master 'repl@10.230.200.19:3306' - retry-time: 60 retries: 1（这个报错等一会就好了。我是这样的，可能是在和主库在连接。我的几秒之后就好了） Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 0 Master_UUID: Master_Info_File: /data/dbdata/3306/data/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: 170216 13:34:19 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) ERROR: No query specified
主从虽然报错了，但是从库的数据缺发生了变化，这里的No query specified这个不算是错误，这个只是我的语法问题show slave status\G;我的后面加了\G,要是给这个\G去了就行了
mysql> select * from t_service;
+----+---------+--------------+----------+-------------+---------------+-------------+---------------+-------------+---------------+
| id | area_id | service | is_alive | create_time | create_person | update_time | update_person | delete_time | delete_person |
+----+---------+--------------+----------+-------------+---------------+-------------+---------------+-------------+---------------+
| 3 | 5 | 君临天下 | 1 | 2016-12-09 | | NULL | NULL | NULL | NULL |
| 4 | 5 | 王者归来 | 1 | 2016-12-09 | | NULL | NULL | NULL | NULL |
+----+---------+--------------+----------+-------------+---------------+-------------+---------------+-------------+---------------+
这个数据已经和主库是一样的了
当我们看到
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
这个就说明主从成功了
还有一种方法就是，我们首先在主库上使用xtrabackup进行一个完整备份
# 备份所有的数据库
[root@client103 backup]# innobackupex --defaults-file=/data/usr/local/mysql56/my.cnf --user=root --password=kongzhong123 --port=3306 --socket=/data/dbdata/3306/mysql.sock /data/bak_dir/mysql/3306/full/ > /data/bak_dir/mysql/3306/innoback.log 2>&1
[root@client103 backup]# innobackupex --defaults-file=/data/usr/local/mysql56/my.cnf --user=root --password=kongzhong123 --port=3306 --socket=/data/dbdata/3306/mysql.sock /data/bak_dir/mysql/3306/full/ > /data/bak_dir/mysql/3306/innoback.log 2>&1
然后把备份的目录拷贝到从库的服务器上（scp）
# 备份完成后，应用日志，是备份的数据保持一致[ /tmp/backup/2014-01-11_14-46-21/：为备份存放的位置]
下面这两部都是在从库上进行的
cd /data/dbdata/3306
mv data data_bak
mkdir data
innobackupex --apply-log --defaults-file=/data/usr/local/mysql56/my.cnf --user=root --password=kongzhong123 --port=3306 --socket=/data/dbdata/3306/mysql.sock /data/bak_dir/mysql/3306/full/2017-02-15_13-58-26
mv data data_bak
mkdir data
innobackupex --apply-log --defaults-file=/data/usr/local/mysql56/my.cnf --user=root --password=kongzhong123 --port=3306 --socket=/data/dbdata/3306/mysql.sock /data/bak_dir/mysql/3306/full/2017-02-15_13-58-26
我在做copy-back这一步总是报错，报错内容为：Original data directory '.' is not empty
网上查找资料说要指定数据文件的目录,所以这里我添加一个--datadir，指定一下路径
innobackupex --copy-back --defaults-file=/data/usr/local/mysql56/my.cnf --socket=/data/dbdata/3306/mysql.sock --datadir=/data/dbdata/3306/data /data/bak_dir/mysql/3306/full/2017-02-15_14-55-03/
网上查找资料说要指定数据文件的目录,所以这里我添加一个--datadir，指定一下路径
innobackupex --copy-back --defaults-file=/data/usr/local/mysql56/my.cnf --socket=/data/dbdata/3306/mysql.sock --datadir=/data/dbdata/3306/data /data/bak_dir/mysql/3306/full/2017-02-15_14-55-03/
chown mysql.mysql data -R
/etc/init.d/mysql start
我们再来看看数据都恢复了
然后我们到备份目录下
cd 2017-02-15_14-55-03/
[root@bao-work-2 2017-02-15_14-55-03]# cat xtrabackup_info
uuid = b933f4aa-f34b-11e6-9420-000c29e8c4e9
name =
tool_name = innobackupex
tool_command = --defaults-file=/data/usr/local/mysql56/my.cnf --user=root --password=... --port=3306 --socket=/data/dbdata/3306/mysql.sock /data/bak_dir/mysql/3306/full/
tool_version = 2.3.3
ibbackup_version = 2.3.3
server_version = 5.6.23-log
start_time = 2017-02-15 14:55:03
end_time = 2017-02-15 14:55:23
lock_time = 0
binlog_pos = filename 'mysql-bin.000003', position '120'
innodb_from_lsn = 0
innodb_to_lsn = 14048848
partial = N
incremental = N
format = file
compact = N
compressed = N
encrypted = N
然后我我们在从这个日志点进行数据同步
具体步骤参考上面的操作
