mysql主从复制 - 孙龙-程序员

mysqlbinlog的学习

[root@localhost 3306]# ll
总用量 128
drwxr-xr-x. 7 mysql mysql  4096 6月  17 15:45 data
-rw-r--r--. 1 mysql mysql  2005 6月  17 2018 my.cnf
-rwx------. 1 mysql mysql  1307 5月  28 02:06 mysql
-rw-rw----. 1 mysql mysql   536 6月  16 05:34 mysql-bin.000006
-rw-rw----. 1 mysql mysql   307 6月  16 06:11 mysql-bin.000007
-rw-rw----. 1 mysql mysql  2827 6月  16 08:14 mysql-bin.000008
-rw-rw----. 1 mysql mysql   126 6月  16 08:14 mysql-bin.000009
-rw-rw----. 1 mysql mysql   150 6月  17 2018 mysql-bin.000010
-rw-rw----. 1 mysql mysql   150 6月  17 2018 mysql-bin.000011
-rw-rw----. 1 mysql mysql   811 6月  17 2018 mysql-bin.000012
-rw-rw----. 1 mysql mysql  1087 6月  17 2018 mysql-bin.000013
-rw-rw----. 1 mysql mysql   150 6月  17 2018 mysql-bin.000014
-rw-rw----. 1 mysql mysql   150 6月  17 2018 mysql-bin.000015
-rw-rw----. 1 mysql mysql   150 6月  17 2018 mysql-bin.000016
-rw-rw----. 1 mysql mysql   150 6月  17 2018 mysql-bin.000017
-rw-rw----. 1 mysql mysql   150 6月  17 11:54 mysql-bin.000018
-rw-rw----. 1 mysql mysql   150 6月  17 12:03 mysql-bin.000019
-rw-rw----. 1 mysql mysql  1656 6月  17 13:04 mysql-bin.000020
-rw-rw----. 1 mysql mysql   126 6月  17 13:04 mysql-bin.000021
-rw-rw----. 1 mysql mysql   617 6月  17 15:33 mysql-bin.000022
-rw-rw----. 1 mysql mysql   150 6月  17 15:34 mysql-bin.000023
-rw-rw----. 1 mysql mysql  2597 6月  17 15:48 mysql-bin.000024
-rw-rw----. 1 mysql mysql   532 6月  17 15:34 mysql-bin.index
-rw-rw----. 1 mysql mysql     5 6月  17 13:40 mysqld.pid
-rw-r-----. 1 mysql mysql 27602 6月  17 13:40 mysql_oldboy3306.err
srwxrwxrwx. 1 mysql mysql     0 6月  17 13:40 mysql.sock

　mysqlbinlog=》解决msyql的binlog日志　用来记录mysql内部的增删改查等，对mysql数据库有更新的内容记录

 -d参数： 截取指定库的binlog

mysqlbinlog /data/mysql/mysql-bin.000023  > a.sql;

mysql -uroot -p123456 -S /data/3306/mysql.sock oldboy < a.sql
-r 参数和重定向 > 是一样的作用

--master-data的作用讲解

　　master-data参数在建立slave数据库的时候会经常用到，因为这是一个比较好用的参数，默认值为1，默认情况下，会包含change master to，这个语句包含file和position的记录始位置。master-data=2的时候，在mysqldump出来的文件包含CHANGE MASTER TO这个语句，处于被注释状态

--master-data=1 是告诉从库从主库哪个位置更新,包含mysql-bin的文件名和起始位置

开始主从复制配置

主从复制原理

主从复制步骤

[root@localhost 3306]# grep -E 'server-id|log-bin' ./my.cnf
log-bin = /data/3306/mysql-bin
server-id = 1

其次建立同步用户

grant replication slave on *.* to 'rep'@'192.168.1.%' identified by 'oldboy123';

select user,host from mysql.user;

show grants for rep@'192.168.10.%';

mysql> show grants for rep@'192.168.1.%';
+--------------------------------------------------------------------------------------------------------------------------+
| Grants for rep@192.168.1.% |
+--------------------------------------------------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'rep'@'192.168.1.%' IDENTIFIED BY PASSWORD '*FE28814B4A8B3309DAC6ED7D3237ADED6DA1E515' |
+--------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

开始备份主库数据库：

flush tables with read lock;//只读锁表

show variables like '%timeout%';

show master status; //记录mysql-bin信息000004 3613

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000006 |      517 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql -uroot -p'oldboy123' -S /data/3306/mysql.sock -e "show master status"；
mysqldump -uroot -poldboy123 -S /data/3306/mysql.sock -A -B --events|gzip > /opt/rep.sql.gz  //手动记录mysql-bin信息000004 3613
或者
mysqldump -uroot -poldboy123 -S /data/3306/mysql.sock -A -B --events --master-data=1 > /opt/rep.sql   //可以查看mysql-bin信息000004 3613
unlock tables;//解锁

#-A备份所有的库  -B 增加 use DB 和 drop等

还有一个办法：
便是在mysql这个数据库相关的那句mysqldump 加上 --lock-tables=0 这个参数，不锁表备份也可以

solve从服务器操作

1，导入已经备份好的数据库

mysql -uroot -poldboy123 -S /data/3307/mysql.sock < /opt/rep.sql //全库导入

执行命令

change master to 
master_host='192.168.1.125',
master_port=3306,
master_user='rep',
master_password='oldboy123',
master_log_file='mysql-bin.000004',
master_log_pos=3613;

　start slave; //启动从服务器复制功能

备注：如果主库备份数据库采用的是 --master-data=1参数，那么在从库这里 change master to 后面不需要制定
master_log_file='mysql-bin.000004',
master_log_pos=3613;
因为这部分已经在还原数据的时候提前应用到数据库里面了

检查从服务器复制功能状态：

  mysql> show slave status\G

   *************************** 1. row ***************************

              Slave_IO_State: Waiting for master to send event
              Master_Host: 192.168.2.222  //主服务器地址
              Master_User: mysync   //授权帐户名，尽量避免使用root
              Master_Port: 3306    //数据库端口，部分版本没有此行
              Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
              Read_Master_Log_Pos: 600     //#同步读取二进制日志的位置，大于等于Exec_Master_Log_Pos
              Relay_Log_File: ddte-relay-bin.000003
              Relay_Log_Pos: 251
              Relay_Master_Log_File: mysql-bin.000004
              Slave_IO_Running: Yes    //此状态必须YES
              Slave_SQL_Running: Yes     //此状态必须YES
                    ......

注：Slave_IO及Slave_SQL进程必须正常运行，即YES状态，否则都是错误的状态(如：其中一个NO均属错误)。

主从服务器测试：

主服务器Mysql，建立数据库，并在这个库中建表插入一条数据：

mysql> create database hi_db;

Query OK, 1 row affected (0.00 sec)

mysql> use hi_db;

Database changed

mysql> create table hi_tb(id int(3),name char(10));

Query OK, 0 rows affected (0.00 sec)

mysql> insert into hi_tb values(001,'bobu');

Query OK, 1 row affected (0.00 sec)

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| hi_db |

| mysql |

| test |

+--------------------+

4 rows in set (0.00 sec)

从服务器Mysql查询：

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| hi_db | //I'M here，大家看到了吧

| mysql |

| test |

+--------------------+

4 rows in set (0.00 sec)

mysql> use hi_db

Database changed

mysql> select * from hi_tb; //查看主服务器上新增的具体数据

+------+------+

| id | name |

+------+------+

| 1 | bobu |

+------+------+

1 row in set (0.00 sec)

知识要点回顾

数据库停止后起不来的问题解决

rm -f /data/3306/mysql.sock /data/3306/*.pid

查看数据库状态,mysql主从复制线程状态说明及用途

Show processlist;

 1 mysql> Show  processlist;
 2 ERROR 2006 (HY000): MySQL server has gone away
 3 No connection. Trying to reconnect...
 4 Connection id:    4
 5 Current database: *** NONE ***
 6 
 7 +----+------+---------------------+-------+-------------+------+-----------------------------------------------------------------------+-------------------+
 8 | Id | User | Host                | db    | Command     | Time | State                                                                 | Info              |
 9 +----+------+---------------------+-------+-------------+------+-----------------------------------------------------------------------+-------------------+
10 |  1 | root | 192.168.1.103:53315 | NULL  | Sleep       | 1664 |                                                                       | NULL              |
11 |  2 | rep  | 192.168.1.125:48754 | NULL  | Binlog Dump |  415 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL              |
12 |  3 | root | 192.168.1.103:59987 | hello | Sleep       |  336 |                                                                       | NULL              |
13 |  4 | root | localhost           | NULL  | Query       |    0 | NULL                                                                  | Show  processlist |
14 +----+------+---------------------+-------+-------------+------+-----------------------------------------------------------------------+-------------------+
15 4 rows in set (0.00 sec)

master

 1 mysql> Show  processlist;
 2 +----+-------------+---------------------+-------+---------+------+-----------------------------------------------------------------------------+-------------------+
 3 | Id | User        | Host                | db    | Command | Time | State                                                                       | Info              |
 4 +----+-------------+---------------------+-------+---------+------+-----------------------------------------------------------------------------+-------------------+
 5 |  2 | root        | 192.168.1.103:53516 | NULL  | Sleep   |  596 |                                                                             | NULL              |
 6 |  3 | root        | localhost           | NULL  | Query   |    0 | NULL                                                                        | Show  processlist |
 7 |  4 | system user |                     | NULL  | Connect |  412 | Waiting for master to send event                                            | NULL              |
 8 |  5 | system user |                     | NULL  | Connect |  337 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL              |
 9 |  6 | root        | 192.168.1.103:59984 | hello | Sleep   |  329 |                                                                             | NULL              |
10 +----+-------------+---------------------+-------+---------+------+-----------------------------------------------------------------------------+-------------------+
11 5 rows in set (0.01 sec)

slave

从服务器授权管理：

主服务器mysql 添加binlog-ignore-db

vim /data/3306/my.cnf 添加忽略的数据库

。

key_buffer_size = 16M
read_buffer_size = 1M
read_rnd_buffer_size = 1M
bulk_insert_buffer_size = 1M
#myisam_sort_buffer_size = 1M
#myisam_max_sort_file_size = 10G
#myisam_max_extra_sort_file_size = 10G
#myisam_repair_threads = 1
#myisam_recover

binlog-ignore-db = mysql
binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema

。。。。。

通过read-only让从库只读访问

参数说明：

1，具有SUPER权限的用户可以更新，不受read-only参数影响，例如：root

2，来自从服务器线程可以更新，不受影响，例如rep用户

主从复制账户权限分配方案：

1，主库和从库使用相同的用户，但是授予不同的权限

　　主库对web用户授权

　　主库： GRANT SELECT ,INSERT,UPDATE,DELETE ON 'web'.* to 'web'@'192.168.1.%' identified by 'oldboy123';

　　从库则收回web用户的权限 REVOKE INSERT,UPDATE,DELETE ON ‘web’.* from 'web'@'192.168.1.%'

　　然后忽略授权库mysql库的同步

　　binlog-ignore-db = mysql
　　binlog-ignore-db = information_schema
　　binlog-ignore-db = performance_schema

　　replicate-ignore-db = mysql

　　注意：这些参数=两边必须有空格

2，主库和从库使用相同的用户，授予相同的权限（非 ALL权限）

主库：GRANT SELECT ,INSERT,UPDATE,DELETE ON 'web'.* to 'web'@'192.168.1.%' identified by 'oldboy123';

　　从库设置read-only参数

　　主库忽略授权同步

　　binlog-ignore-db = mysql
　　binlog-ignore-db = information_schema
　　binlog-ignore-db = performance_schema

　　replicate-ignore-db = mysql

主从插入重复的错误或者不同步故障解决：

主库增加user表插入数据；从库正常同步。假如我删除了从库的user表，然后在主库user表中插入新数据。

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.125
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000008
          Read_Master_Log_Pos: 968
               Relay_Log_File: relay-bin.000007
                Relay_Log_Pos: 886
        Relay_Master_Log_File: mysql-bin.000008
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: mysql
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 1146
                   Last_Error: Error 'Table 'hello.user' doesn't exist' on query. Default database: 'hello'. Query: 'INSERT INTO `user` 
(`name`) VALUES ('9')'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 740
              Relay_Log_Space: 1410
              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: 1146
               Last_SQL_Error: Error 'Table 'hello.user' doesn't exist' on query. Default database: 'hello'. Query: 'INSERT INTO `user`
 (`name`) VALUES ('9')'
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
1 row in set (0.00 sec)

这个时候怎么解决了？

stop slave;//临时停止同步开关

set global sql_slave_skip_counter = 1;//将同步指针向下移一个，如果多次不同步，可以重复操作

start slave;

把主库的user表复制到从库；然后在主库user新增数据，查看从库，是ok的；

Mysql从库记录binlog [级联]

一主多从宕机从库切换主继续和从同步过程：

主库master donw机

从库slave down机

Msyql官方同步架构图

主主复制

3307

[mysqld]

auto_increment_increment = 2

auto_increment_offset = 2

log-bin = /data/3307/msyql-bin

log-slave-updates

expire_logs_days = 7

[mysqld]

3306

[mysqld]

auto_increment_increment = 2

auto_increment_offset = 1

log-bin = /data/3306/msyql-bin

log-slave-updates

expire_logs_days = 7

[mysqld]

重启数据库

/data/3306/msyql stop

/data/3306/msyql start

/data/3307/msyql stop

/data/3307/msyql start

导入数据库

3307

msyqldump -uroot -p'oldboy123'-S /data/3307/mysql.sock -A -B --master-data=1 -x --events > /opt/3307bak.sql

3306

msyqldump -uroot -p'oldboy123'-S /data/3306/mysql.sock < /opt/3307bak.sql

3306 执行以下

change master to

master_host="10.0.0.7,

master_port=3307,

master_user='rep',

master_password='oldboy123;

此处没有加binlog位置因为备份库的时候用的—master-data=1

查看状态

start slave;

show slave status\G;

在进行测试…

posted on 2018-06-10 21:41 孙龙-程序员阅读(263) 评论(0) 编辑收藏举报

刷新页面返回顶部