mysql 主从复制

oi622

MySQL复制：

        扩展：
                scale on: 向上扩展，垂直扩展
                scale out：向外扩展，水平扩展

                1，4G：50 concurrent
                2*8=16, 32G , 300

        MySQL保存二进制日志：
                statement
                row
                mixed

                默认为异步工作模式

                SLAVE:
                        IO thread: 向主服务请求二进制日志中的事件
                        SQL thread：从中继日志读取事件并在本地执行

                MASTER:
                        binlog dump: 将IO thread请求的事件发送给对方；

                工作架构：
                        从服务器：有且只能有一个主服务器；
                        MariaDB-10：支持多主模型，多源复制(multi-source replication)
                        一主多从：

                读写分离：主从模型下，让前端分发器能识别读/写，并且按需调度至目标主机；
                        amoeba:
                        mysql-proxy：

                双主：master-master
                        1、必须设定双方的自动增长属性，以避免冲突
                                auto_increment_increment=#
                                        定义自动增长字段起始值
                                auto_increment_offset=2
                                        步长
                        2、数据不一致；
                                Age, Salary

                                A： update t1 set Salary=salary+1000 WHERE Age>=30;
                                B:  update t1 set Age=Age-3 WHERE Salary < 3000;

                        功能：均衡读请求；写请求双方一样；

                示例：主从复制的配置

                        版本
                           1、双方的MySQL要一致；
                        2、如果不一致：主的要低于从的；
        从哪儿开始复制：
                                1、都从0开始：
                                2、主服务器已经运行一段时间，并且存在不小的数据集：
                                把主服务器备份，然后在从服务恢复，从主服务器上备份时所处的位置开始复制；




配置过程：


        【准备】一台最小化安装CentOS6.7，编译安装好mysql，关机保存快照


        【链接克隆！ 在clone的服务器上操作下面的内容】{
        【克隆后网卡问题】

1 2 3 4 5 6 7 8 9 10 11 12 13

1、[iyunv@localhost ~]# ifconfig -a    [iyunv@localhost ~]# vim /etc/udev/rules.d/70-persistent-net.rules         删除第一个MAC对应的不存在的记录，把下面的eth1改为eth0 [iyunv@localhost ~]# vim /etc/sysconfig/network-scripts/ifcfg-eth0    删除MAC地址与UUID，修改IP地址， [iyunv@localhost ~]# modprobe -r e1000 [iyunv@localhost ~]# modprobe e1000 [iyunv@localhost ~]# service network restart 重载网卡驱动，重启网络服务 [iyunv@localhost ~]# ifconfig eth0      Link encap:Ethernet 拍摄快照，OK！

【配置主服务器：192.168.81.132】
                1、改server-id
                2、启用二进制日志
                3、创建有复制权限的帐号

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

[iyunv@localhost ~]# service iptables stop [iyunv@localhost ~]# service mysqld stop [iyunv@localhost ~]# vim /etc/my.cnf datadir= /mydata/data log-bin=/mydata/binlogs/master-bin server-id       = 1 [iyunv@localhost ~]# mkdir -p /mydata/binlogs [iyunv@localhost ~]# chown -R mysql.mysql /mydata/binlogs [iyunv@localhost ~]# service mysqld restart [iyunv@localhost ~]# mysql -p mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repluser'@'192.168.%.%' IDENTIFIED BY 'replpass'; Query OK, 0 rows affected (0.00 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.01 sec) mysql> SHOW GLOBAL VARIABLES LIKE '%RELAY%'; 登录主服务器的mysql，查询master的状态 mysql> show master status; +-------------------+----------+--------------+------------------+ | File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | +-------------------+----------+--------------+------------------+ | master-bin.000001 |      442 |              |                  | +-------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) 注：执行完此步骤后不要再操作主服务器MYSQL，防止主服务器状态值变化！

【配置 从服务器：192.168.81.133】
        1、改server-id
        2、启用中继日志
        3、连接主服务器
        4、启动复制线程

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55

[iyunv@localhost ~]# service iptables stop    [iyunv@localhost ~]# vim /etc/my.cnf # log-bin=mysql-bin # binlog_format=mixed server-id       = 11 relay_log       = /mydata/relaylogs/relay-bin [iyunv@localhost ~]# vim /etc/my.cnf [iyunv@localhost ~]# mkdir -pv /mydata/relaylogs/ mkdir: 已创建目录 "/mydata/relaylogs/" [iyunv@localhost ~]# chown -R mysql:mysql /mydata/relaylogs/ [iyunv@localhost ~]# service mysqld restart 查看中继日志是否启用 [iyunv@localhost ~]# mysql -p mysql> SHOW GLOBAL VARIABLES LIKE '%RELAY%'; +-----------------------+-----------------------------+ | Variable_name         | Value                       | +-----------------------+-----------------------------+ | max_relay_log_size    | 0                           | | relay_log             | /mydata/relaylogs/relay-bin | | relay_log_index       |                             | | relay_log_info_file   | relay-log.info              | | relay_log_purge       | ON                          | | relay_log_recovery    | OFF                         | | relay_log_space_limit | 0                           | | sync_relay_log        | 0                           | | sync_relay_log_info   | 0                           | +-----------------------+-----------------------------+ 9 rows in set (0.00 sec) 此时没有任何数据 [iyunv@localhost ~]# ll /mydata/relaylogs/ 总用量 0 mysql> help CHANGE MASTER TO mysql> mysql> CHANGE MASTER TO  MASTER_HOST='192.168.81.132',  MASTER_USER='repluser', MASTER_PASSWORD='replpass',MASTER_LOG_FILE = 'master-bin.000001',MASTER_LOG_POS = 442; Query OK, 0 rows affected (0.02 sec) 启动从服务器 mysql> START SLAVE; 【得等一会儿就会自动连上主服务器】 mysql> SHOW SLAVE STATUS \G        Slave_IO_State: Waiting for master to send event           Master_Host: 192.168.81.132           Master_User: repluser           Master_Port: 3306         Connect_Retry: 60       Master_Log_File: master-bin.000001   Read_Master_Log_Pos: 527        Relay_Log_File: relay-bin.000002         Relay_Log_Pos: 339 Relay_Master_Log_File: master-bin.000001      Slave_IO_Running: Yes     Slave_SQL_Running: Yes

【验证】

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35

主服务器创建一个数据库 mydb1 mysql> create database mydb1; mysql> use mydb1; mysql> create table t1 (id int); mysql> insert into t1 values (1),(10); mysql> show master status; +-------------------+----------+--------------+------------------+ | File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | +-------------------+----------+--------------+------------------+ | master-bin.000001 |      989 |              |                  | +-------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) 从服务器上可以看到数据库 mydb1 mysql> select * from mydb1.t1; +------+ | id   | +------+ |    1 | |    1 | |   10 | +------+ mysql>  show slave status\G Slave_IO_State: Waiting for master to send event Master_Host: 192.168.81.132 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-bin.000001 Read_Master_Log_Pos: 989 【功能实现！】

重启mysqld会自动启动slave ！

1 2 3 4 5 6 7 8 9 10 11 12 13

[iyunv@localhost ~]# service mysqld restart [iyunv@localhost ~]# mysql -p Enter password: mysql> start slave; Query OK, 0 rows affected, 1 warning (0.01 sec) mysql> show warnings; +-------+------+--------------------------+ | Level | Code | Message                  | +-------+------+--------------------------+ | Note  | 1254 | Slave is already running | +-------+------+--------------------------+ 1 row in set (0.02 sec)

【在已经产生数据的mysql服务器，做mysql复制 演示】
过程：
1，模拟主服务器产生大量的数据
2，使用mysqldump 刷新日志，把所有的数据备份出来，查看新的日志文件和位置
3，关闭slave进程，在干净的从服务器导入这个备份的数据文件，开启slave进程
4，在主服务器上产生新的数据，在从服务器上验证！


主服务器操作！

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33

【删除主服务器的数据库】 mysql> drop database mydb1; Query OK, 1 row affected (0.01 sec) mysql> \q Bye 【模拟主服务器已经产生大量的数据了，导入数据进来】 [iyunv@localhost ~]# mysql -p < /root/hellodb.sql mysql> show master status; +-------------------+----------+ | File              | Position | +-------------------+----------+ | master-bin.000001 |     8833 | +-------------------+----------+ mysql> create database mydb; mysql> use hellodb; mysql> create table t1 (id int); mysql> \q Bye 【OK，让从服务器从这个状态来复制】 dump出来， [iyunv@localhost ~]# mysqldump -u root  -p  --all-databases --flush-logs --master-data=2 --lock-all-tables --events > all.sql 刷新日志文件，让从服务器从新的位置开始复制 [iyunv@localhost ~]# head -n 23 all.sql -- CHANGE MASTER TO MASTER_LOG_FILE='master-bin.000005', MASTER_LOG_POS=107; [iyunv@localhost ~]# scp all.sql 192.168.81.133:/root/ root@192.168.81.133's password: all.sql             100%  544KB 543.7KB/s   00:00    [iyunv@localhost ~]#

从服务器操作！

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29

【在从服务器上恢复数据】 【删除从服务器的数据库，恢复到原始】 mysql> stop slave; mysql> drop database mydb1; Query OK, 1 row affected (0.02 sec) [iyunv@localhost ~]# mysql -p < all.sql Enter password: 连接到mysql，启动slave进程 mysql> stop slave; mysql> CHANGE MASTER TO  MASTER_HOST='192.168.81.132',  MASTER_USER='repluser', MASTER_PASSWORD='replpass',MASTER_LOG_FILE = 'master-bin.000005',MASTER_LOG_POS = 107; mysql> start slave; mysql> show slave status\G                                                                                        Slave_IO_State: Waiting for master to send event           Master_Host: 192.168.81.132           Master_User: repluser           Master_Port: 3306         Connect_Retry: 60       Master_Log_File: master-bin.000005   Read_Master_Log_Pos: 556787        Relay_Log_File: relay-bin.000002         Relay_Log_Pos: 556934 Relay_Master_Log_File: master-bin.000005      Slave_IO_Running: Yes     Slave_SQL_Running: Yes

【验证】

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33

主服务器创建一个数据库 mysql> create database haha; Query OK, 1 row affected (0.00 sec) mysql> show databases; +--------------------+ | Database           | +--------------------+ | information_schema | | haha               | | hellodb            | | mysql              | | performance_schema | | test               | +--------------------+ 6 rows in set (0.01 sec) 从服务器查看 mysql> show databases; +--------------------+ | Database           | +--------------------+ | information_schema | | haha               | | hellodb            | | mysql              | | performance_schema | | test               | +--------------------+ 6 rows in set (0.03 sec) mysql复制过程，演示结束。