mysql主从复制配置

17.1-17.5 mysql主从复制的配置与测试

mysql主从配置是提高数据安全性的方式，应用很广泛。其原理是：
1 主把数据库的更改记录放在二进制日志binlog里
2 从把主的binlog同步到本机上并记录在中继日志reylaylog里
3 从根据relaylog里的sql语句按顺序执行

从上有两个线程，一个负责把主的binlog同步并生成relaylog，另一个负责把relaylog里的sql语句落地执行。

主的设置

 安装mysql
 修改my.cnf，增加server-id=128和log_bin=centos01
 修改完配置文件后，启动或者重启mysqld服务
 把mysql库备份并恢复成aming库，作为测试数据
 mysqldump -uroot mysql > /tmp/mysql.sql
 mysql -uroot -e “create database aming”
 mysql -uroot aming < /tmp/mysql.sql

 创建用作同步数据的用户
 grant replication slave on *.* to 'repl'@'192.168.27.129' identified by '123123';
 flush tables with read lock;

 mysql> show master status;  //记住以下内容
+-----------------+----------+--------------+------------------+-------------------+
| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
| centos01.000045 |  1662153 |              |                  |                   |
+-----------------+----------+--------------+------------------+-------------------+

从的配置

 安装mysql
 查看my.cnf，配置server-id=129，要求和主不一样   //这里不需要配置bin_log，因为从上不需要这东西
 修改完配置文件后，启动或者重启mysqld服务
 把主上aming库同步到从上，可以先创建aming库，然后把主上的/tmp/mysql.sql拷贝到从上，然后导入aming库
 scp 192.168.27.128:/tmp/mysql.sql /tmp/   //复制备份文件到本机
 mysql -uroot -e 'create database aming'
 mysql -uroot aming </tmp/mysql.sql  
 stop slave  //停止从机
 change master to master_host='192.168.27.128', master_user='repl', master_password='123123', master_log_file='centos01.000045', master_log_pos=1662153  //设置主从的关键一步
 start slave  //开启从机
 还要到主上执行 unlock tables   //开启主机


mysql> show slave status\G
************* 1. row ****************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.27.128
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: centos01.000045
          Read_Master_Log_Pos: 1662153
               Relay_Log_File: cent02-relay-bin.000002
                Relay_Log_Pos: 282
        Relay_Master_Log_File: centos01.000045
             Slave_IO_Running: Yes   //见到以下两项为yes就是配置成功
            Slave_SQL_Running: Yes

故障解除
当从机的数据意外操作与主机不同后，需要重新执行以下语句并更改当时的log文件以及位置。

change master to master_host='192.168.27.128', master_user='repl', master_password='123123', master_log_file='centos01.000045', master_log_pos=1662153

重启
如果重启master，那么需要先stop slave。master重启结束后再start slave。否则这种主从复制关系会很容易的被破坏。

配置参数

 //主服务器上
 binlog-do-db=      //仅同步指定的库
 binlog-ignore-db=  //忽略指定库

 //从服务器上
 replicate_do_db=
 replicate_ignore_db=
 replicate_do_table=
 replicate_ignore_table=
 replicate_wild_do_table=   //如aming.%, 支持通配符% 
 replicate_wild_ignore_table=

如果遇上了联合查询，前面的设置都不可靠。所以推荐用最后两个设置，同时他们的命名方式更加灵活。

操作mysql用户

drop user 'user'@'hostname';   //删除用户

show grants for 'user'@'hostname';  //查看用户的权限