mysql 主从同步

mysql cmake install 过程我在这边就不说了，如果要看的朋友，点击我以前的博客 ，mysql version : 5.6.13
http://blog.youkuaiyun.com/wanglei_storage/article/details/48262141

下一篇是mysql 主从同步 + atlas读写分离，也就是目前比较热门的技术了
http://blog.youkuaiyun.com/wanglei_storage/article/details/48808777

---

好，下面进入正题。通过两台Server实现MySQL主从同步
我的主和从库的conf档在文章尾部，可以翻到最下面查看！

mysql master： 192.168.1.202
mysql slave ： 192.168.1.203

一、mysql master操作

1、赋权用户、并且添加库和表

ok，先添加主从同步用户，接着随意创建一个库再添加一只表

mysql> grant replication slave on *.* to 'slave'@'192.168.1.203' identified by 'slave';

mysql> create database slave;
mysql> use slave;

mysql> create table blog_user
    -> (
    -> user_Name char(15) not null check(user_Name !=''),
    -> user_Password char(15) not null,
    -> user_emial varchar(20) not null unique,
    -> primary key(user_Name)  
    -> );
Query OK, 0 rows affected (0.36 sec)

2、备份刚才所创建的库，并且进行还原

这两部不能省略，不然等下会报错，关于权限问题
mkdir slave
chmod 777 slave

(1)、备份表结构
time /usr/local/mysql/bin/mysqldump -uroot –set-gtid-purged=OFF –compact –single-transaction –events –triggers –routines –master-data=2 -B slave -d > /tmp/slave.sql

(2)、备份表数据
time /usr/local/mysql/bin/mysqldump -uroot –skip-tz-utc –compact –single-transaction –events –triggers –routines –flush-logs –master-data=2 slave -T /tmp/slave | tee /tmp/slave.log
该命令会导出表数据及log

好，下面我们整理下刚才导出的文件，准备导入到从库中
/tmp/slave.log 日志
/tmp/slave.sql 表结构
/tmp/slave blog_user.sql blog_user.txt 表数据

打包，并丢到 mysql slave上面
time tar zcvf slave.tgz slave slave.log slave.sql
slave/
slave/blog_user.txt
slave/blog_user.sql
slave.log
slave.sql

scp slave.tgz 192.168.1.203:/tmp

---

二、mysql slave

1、解压刚才从mysql master拷贝过来的slave.tgz 数据

tar zxf slave.tgz

2、导入表结构和表数据

当然在导入之前呢，先在从库上面创建slave库
create database slave;

(1)、导入表结构
time mysql -uroot slave < /tmp/slave.sql

(2)、导入表数据
time /usr/local/mysql/bin/mysqlimport -uroot slave –local /tmp/slave/*.txt

ok 导入完成！

三、实现主从同步

1、执行同步操作

my.conf已经配置完成，并且数据已经导过去了，现在就需要同步了
在mysql slave进行操作

mysql> slave stop;
mysql> reset master;
mysql> reset slave;
mysql> set global gtid_purged='9be88fe9-c6f2-11e4-997f-000c29ab6f59:1-4';

mysql> change master to
    -> master_host='192.168.1.202',
    -> master_user='slave',
    -> master_password='slave',
    -> master_port=3306,
    -> master_auto_position = 1;

mysql> start slave;

2、查看主从是否已经同步

主从环境基本搭建完成，现在要做的是验证了！！
查看Slave_SQL 和 Slave_IO状态：
Slave_SQL 是主从同步bin-log是否正常
Slave_IO 是本地relay-log状态是否正常

mysql> show slave status\G;  
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
双yes表示主从已经正常执行同步了

3、验证
好，下面我们在主库中创建几个表，看从库是否会同步

主库操作：
> mysql -u root
mysql> show slave hosts;
+-----------+---------------+------+-----------+--------------------------------------+
| Server_id | Host          | Port | Master_id | Slave_UUID                           |
+-----------+---------------+------+-----------+--------------------------------------+
|         2 | 192.168.1.203 | 3306 |         1 | 74cf34c4-c6f7-11e4-999e-000c2933d19c |
+-----------+---------------+------+-----------+--------------------------------------+
mysql> use slave;
mysql> create table test(user char(48),pass char(48));
mysql> create table haha(user char(48),pass char(48));

从库操作：

> mysql -u root
mysql> show databases;
mysql> use slave;
mysql> show tables;
+-----------------+
| Tables_in_slave |
+-----------------+
| blog_user       |
| haha            |
| test            |
+-----------------+

ok，发现刚才从主库创建的表在从库中已经同步出来！！

---

mysql master my.cnf档

[mysqld]

basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
log-error = /usr/local/mysql/mysql_error.log
pid-file = /usr/local/mysql/mysql.pid
user = mysql
tmpdir = /tmp


server-id = 1
skip-character-set-client-handshake                         #忽略应用程序想要设置的其他字符集
init-connect='SET NAMES utf8'                               #连接时执行的SQL
character-set-server=utf8                                   #服务端默认字符集
wait_timeout=1800                                           #请求的最大连接时间
interactive_timeout=1800                                    #和上一参数同时修改才会生效
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES         #sql模式
log-bin = mysql-bin                                         #打开二进制功能,MASTER主服务器必须打开此项
relay-log = mysql-bin                                       #文件名格式
relay-log-index = mysql-bin.index                           #index文件名MySQL 5.6.10版本提供了更方便的基于GTID的复制功能，MySQL可以通过GTID自动识别上次同步的点，极大地方便了运维人员，减少出错的几率。
binlog-format=ROW                                           #binlog格式
log-slave-updates=true                                      #表示如果一个MASTER挂掉的话，另外一个马上接管
gtid-mode=on                                                #用于启动GTID及满足附属的其它需求
enforce-gtid-consistency=true                                           
master-info-repository=TABLE                                #启用此两项，可用于实现在崩溃时保证二进制及从服务器安全的功能
relay-log-info-repository=TABLE                                         
sync-master-info=1                                          #启用之可确保无信息丢失
slave-parallel-workers=2                                    #设定从服务器的SQL线程数；0表示关闭多线程复制功能
binlog-checksum=CRC32                                                   
master-verify-checksum=1                                                
slave-sql-verify-checksum=1                                 #启用复制有关的所有校验功能
binlog-rows-query-log_events=1                              #MySQL 5.6.10版本提供了更方便的基于GTID的复制功能，MySQL可以通过GTID自动识别上次同步的点，极大地方便了运维人员，减少出错的几率。
expire_logs_day=5                                           #超过5天的binlog删除
max_binlog_size=1024M
replicate-ignore-db = mysql                                 #忽略不同步主从的数据库
replicate-ignore-db = information_schema
replicate-ignore-db = performance_schema
replicate-ignore-db = test

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

---

mysql slave my.cnf档

[mysqld]

basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
log-error = /usr/local/mysql/mysql_error.log
pid-file = /usr/local/mysql/mysql.pid
user = mysql
tmpdir = /tmp


server-id = 2
skip-character-set-client-handshake                      #忽略应用程序想要设置的其他字符集
init-connect='SET NAMES utf8'                            #连接时执行的SQL
character-set-server=utf8                                #服务端默认字符集
wait_timeout=1800                                        #请求的最大连接时间
interactive_timeout=1800                                 #和上一参数同时修改才会生效
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES      #sql模式
log-bin = mysql-bin                                      #打开二进制功能,MASTER主服务器必须打开此项
relay-log = mysql-bin                                    #文件名格式
relay-log-index = mysql-bin.index                        #index文件名MySQL 5.6.10版本提供了更方便的基于GTID的复制功能，MySQL可以通过GTID自动识别上次同步的点，极大地方便了运维人员，减少出错的几率。
binlog-format=ROW                                        #binlog格式
log-slave-updates=true                                   #表示如果一个MASTER挂掉的话，另外一个马上接管
gtid-mode=on                                             #用于启动GTID及满足附属的其它需求
enforce-gtid-consistency=true                                           
report-port=3306                                         #从属服务器的端口
report-host=192.168.1.203                                #从属服务器的主机名
master-info-repository=TABLE                             #启用此两项，可用于实现在崩溃时保证二进制及从服务器安全的功能
relay-log-info-repository=TABLE                                         
sync-master-info=1                                       #启用之可确保无信息丢失
slave-parallel-workers=0                                 #设定从服务器的SQL线程数；0表示关闭多线程复制功能
binlog-checksum=CRC32                                                   
master-verify-checksum=1                                                
slave-sql-verify-checksum=1                              #启用复制有关的所有校验功能
binlog-rows-query-log_events=1                           #MySQL 5.6.10版本提供了更方便的基于GTID的复制功能，MySQL可以通过GTID自动识别上次同步的点，极大地方便了运维人员，减少出错的几率。
expire_logs_day=5                                        #超过5天的binlog删除
max_binlog_size=1024M
replicate-ignore-db = mysql                                 #忽略不同步主从的数据库
replicate-ignore-db = information_schema
replicate-ignore-db = performance_schema
replicate-ignore-db = test

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES