Mysql主从同步(1)

hao1nan

为了测试效果，先在master机器上创建测试库　　
mysql> CREATE DATABASE huanqiu CHARACTER SET utf8 COLLATE utf8_general_ci;
　　
Query OK, 1 row affected (0.00 sec)
　　
mysql> use huanqiu;
　　
Database changed
　　
mysql> create table if not exists haha (id int(10) PRIMARY KEY AUTO_INCREMENT,name varchar(50) NOT NULL);
　　
Query OK, 0 rows affected (0.02 sec)
　　
mysql> insert into huanqiu.haha values(1,"wangshibo"),(2,"guohuihui");
　　
Query OK, 2 rows affected (0.00 sec)
　　
Records: 2  Duplicates: 0  Warnings: 0
　　
mysql> select * from huanqiu.haha;
　　
+----+-----------+

　　
|>　　
+----+-----------+
　　
|  1 | wangshibo |
　　
|  2 | guohuihui |
　　
+----+-----------+
　　
2 rows in set (0.00 sec)
　　
--------------------------------------------------------------------
　　
温馨提示：
　　
修改库或表的字符集

　　
mysql>>
　　
mysql>>　　
添加主键

　　
mysql>>
　　
mysql>>　　
删除主键时要先删除自增长，再删除主键

　　
mysql>>
　　
mysql>>　　
--------------------------------------------------------------------
　　
下面是master数据库上的操作：
　　
1）设置master数据库的my.cnf文件（在[mysqld]配置区域添加下面内容）
　　
[iyunv@master ~]# vim /usr/local/mysql/my.cnf
　　
.......
　　
server-id=1        #数据库唯一ID，主从的标识号绝对不能重复。
　　
log-bin=mysql-bin    #开启bin-log，并指定文件目录和文件名前缀
　　
binlog-do-db=huanqiu  #需要同步的数据库。如果是多个同步库，就以此格式另写几行即可。如果不指明对某个具体库同步，就去掉此行，表示同步所有库（除了ignore忽略的库）。
　　
binlog-ignore-db=mysql  #不同步mysql系统数据库。如果是多个不同步库，就以此格式另写几行；也可以在一行，中间逗号隔开。
　　
sync_binlog = 1      ＃确保binlog日志写入后与硬盘同步
　　
binlog_checksum = none  ＃跳过现有的采用checksum的事件，mysql5.6.5以后的版本中binlog_checksum=crc32,而低版本都是binlog_checksum=none
　　
binlog_format = mixed   ＃bin-log日志文件格式，设置为MIXED可以防止主键重复。
　　
--------------------------------------------------------------------------------
　　
温馨提示：在主服务器上最重要的二进制日志设置是sync_binlog，这使得mysql在每次提交事务的时候把二进制日志的内容同步到磁盘上，即使服务器崩溃也会把事件写入日志中。
　　
sync_binlog这个参数是对于MySQL系统来说是至关重要的，他不仅影响到Binlog对MySQL所带来的性能损耗，而且还影响到MySQL中数据的完整性。对于"sync_binlog"参数的各种设置的说明如下：
　　
sync_binlog=0，当事务提交之后，MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘，而让Filesystem自行决定什么时候来做同步，或者cache满了之后才同步到磁盘。
　　
sync_binlog=n，当每进行n次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。
　　
在MySQL中系统默认的设置是sync_binlog=0，也就是不做任何强制性的磁盘刷新指令，这时候的性能是最好的，但是风险也是最大的。因为一旦系统Crash，在binlog_cache中的所有binlog信息都会被丢失。而当设置为“1”的时候，是最安全但是性能损耗最大的设置。因为当设置为1的时候，即使系统Crash，也最多丢失binlog_cache中未完成的一个事务，对实际数据没有任何实质性影响。
　　
从以往经验和相关测试来看，对于高并发事务的系统来说，“sync_binlog”设置为0和设置为1的系统写入性能差距可能高达5倍甚至更多。
　　
--------------------------------------------------------------------------------
　　
2）导出master数据库多余slave数据库中的数据，然后导入到slave数据库中。保证双方在同步环境实现前的数据一致。
　　导出数据库之前先锁定数据库
　　mysql> flush tables with read lock;    #数据库只读锁定命令，防止导出数据库的时候有数据写入。unlock tables命令解除锁定
　　导出master数据库中多余的huanqiu库(master数据库的root用户登陆密码：123456)

[iyunv@master ~]# mysqldump -uroot huanqiu -p123456 >/opt/huanqiu.sql

[iyunv@master ~]# rsync  -e "ssh -p22" -avpgolr /opt/huanqiu.sql 182.148.15.237:/opt/   ＃将导出的sql文件上传到slave机器上

　　
3）设置数据同步权限

　　mysql> grant replication slave,replication client on *.* to slave@'182.148.15.237'>　　Query OK, 0 rows affected (0.02 sec)
　　mysql> flush privileges;
　　Query OK, 0 rows affected (0.00 sec)
　　

　　
--------------------------------------------------------------------------------
　　
温馨提示：
　　
权限查看方式
　　
mysql> show grants;
　　
mysql> show grants for slave@'182.148.1115.237';
　　
--------------------------------------------------------------------------------
　　

　　
4）查看主服务器master状态(注意File与Position项，从服务器需要这两项参数)
　　
mysql> show master status;
　　
+------------------+----------+--------------+------------------+-------------------+
　　
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
　　
+------------------+----------+--------------+------------------+-------------------+
　　
| mysql-bin.000007 |      120 | huanqiu      | mysql            |                   |
　　
+------------------+----------+--------------+------------------+-------------------+
　　
1 row in set (0.00 sec)
　　
下面是slave数据库上的操作：
　　
1）设置slave数据库的my.cnf配置文件
　　
[iyunv@master ~]# vim /usr/local/mysql/my.cnf
　　
.......
　　
server-id=2   #设置从服务器id，必须于主服务器不同
　　
log-bin=mysql-bin   #启动MySQ二进制日志系统
　　
replicate-do-db=huanqiu  #需要同步的数据库名。如果不指明同步哪些库，就去掉这行，表示所有库的同步（除了ignore忽略的库）。
　　
replicate-ignore-db=mysql  #不同步mysql系统数据库
　　
slave-skip-errors = all   #跳过所有的错误错误，继续执行复制操作
　　
-----------------------------------------------------------------------------------------------
　　
温馨提示：
　　
当只针对某些库的某张表进行同步时，如下，只同步huanqiu库的haha表和huanpc库的heihei表：
　　
replicate-do-db = huanqiu
　　
replicate-wild-do-table = huanqiu.haha       //当只同步几个或少数表时，可以这样设置。注意这要跟上面的库指定配合使用；
　　
replicate-do-db = huanpc
　　
replicate-wild-do-table = huanpc.heihei      //如果同步的库的表比较多时，就不能这样一一指定了，就把这个选项配置去掉，直接根据指定的库进行同步。
　　
-----------------------------------------------------------------------------------------------
　　
2）在slave数据库中导入从master传过来的数据。
　　
mysql> CREATE DATABASE huanqiu CHARACTER SET utf8 COLLATE utf8_general_ci;   #先创建一个huanqiu空库，否则下面导入数据时会报错说此库不存在。
　　
mysql> use huanqiu;
　　
mysql> source /opt/huanqiu.sql;   ＃导入master中多余的数据。
　　
.......
　　
3）配置主从同步指令
　　
mysql> stop slave;   ＃执行同步前，要先关闭slave
　　
mysql> change  master to master_host='182.148.15.238',master_user='slave',master_password='slave@123',master_log_file='mysql-bin.000007',master_log_pos=120;
　　
mysql> start slave;
　　
mysql> show slave status \G;
　　
.......
　　
*************************** 1. row ***************************
　　Slave_IO_State: Waiting for master to send event
　　Master_Host: 182.148.15.238
　　Master_User: slave
　　Master_Port: 3306
　　Connect_Retry: 60
　　Master_Log_File: mysql-bin.000007
　　Read_Master_Log_Pos: 120
　　Relay_Log_File: mysql-relay-bin.000002
　　Relay_Log_Pos: 279
　　Relay_Master_Log_File: mysql-bin.000007
　　Slave_IO_Running: Yes
　　Slave_SQL_Running: Yes
　　Replicate_Do_DB: huanqiu
　　Replicate_Ignore_DB: mysql
　　.............
　　Seconds_Behind_Master: 0
　　
如上，当IO和SQL线程的状态均为Yes，则表示主从已实现同步了！
　　
查看slave数据库中的数据情况
　　
mysql> show databases;
　　
+--------------------+
　　
| Database           |
　　
+--------------------+
　　
| information_schema |
　　
| huanqiu            |
　　
| mysql              |
　　
| performance_schema |
　　
| test               |
　　
+--------------------+
　　
5 rows in set (0.00 sec)
　　
mysql> select * from huanqiu.haha;
　　
+----+-----------+

　　
|>　　
+----+-----------+
　　
|  1 | wangshibo |
　　
|  2 | guohuihui |
　　
+----+-----------+
　　
2 rows in set (0.00 sec)
　　
下面测试下Mysql主从同步的效果
　　
现在主数据库上写入新数据
　　
mysql> unlock tables;    #解锁，否则新数据无法写入
　　
mysql> insert into huanqiu.haha values(100,"anhui");
　　
Query OK, 1 row affected (0.00 sec)
　　
然后在slave数据库上查看，发现master上新写入的数据已经同步过来了
　　
mysql> select * from huanqiu.haha;
　　
+-----+-----------+

　　
|>　　
+-----+-----------+
　　
|   1 | wangshibo |
　　
|   2 | guohuihui |
　　
| 100 | anhui     |
　　
+-----+-----------+
　　
3 rows in set (0.00 sec)
　　
至此，主从同步环境已经实现！