• 博客访问: 613611
  • 博文数量: 290
  • 用 户 组: 普通用户
  • 注册时间: 2007-12-01 11:01
  • 认证徽章:
个人简介

暂无介绍

文章分类

全部博文(290)

文章存档

2016年(5)

2013年(1)

2012年(6)

2011年(7)

2010年(1)

2009年(12)

2008年(67)

2007年(142)

2006年(21)

2005年(28)

我的朋友

分类: MySQL

2016-06-24 18:53:12

   使用mysql,容灾是经常要考虑的事,主从复制是比较常见的一种容灾机制,类似oracle的OGG,只是还要简单很多
   利用主从复制可以搭建很复杂的主从环境,比如主-从(1)-从,主-从(2)-从等等,这里只做一个简单的例子,即简单的主-从环境,旨在掌握好原理,即可
   本环境的ip规划如下:主(192.168.1.130)、从(192.168.1.131)
   不絮叨,我们直奔主题吧

    一、主库上的操作
    1、修改/etc/my.cnf 增加log-bin、server-id,我的my.cnf如下

[root@ct-test1 ~]# cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/mysql/data
socket=/var/lib/mysql/mysql.sock


# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
log-bin=mysql-bin
server-id=101

这里需要说的是server-id修改后,mysql的数据库就不同于原库了,所以不要乱改哈

    2、创建复制用户repl
    这个只需要在主库上执行,因为下文是将整个数据库导入备库,所以用户也是会同步过去的(如果不是全库备份,就需要在备库上执行此步骤)
    这里有三步:
        a、创建用户;
        b、给予权限(我这里给了所有权限,按照官方文档中只给予GRANT REPLICATION SLAVE ON *.* TO user 的权限,但在实际同步的过程中,由于同步用户没有特殊权限,比如创建表、数据库等,就会报错)
        c、flush PRIVILEGES,此步骤是确保权限可以及时跟新
mysql> CREATE USER repl IDENTIFIED BY 'Zxt1234!';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO repl;
Query OK, 0 rows affected (0.02 sec)

mysql> GRANT ALL ON *.* TO repl;
Query OK, 0 rows affected (0.01 sec)

mysql> flush PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
    
    3、查看master的Binary Log
    这里需要记住这两个字段file和position,回头启动复制的时候会用到
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

    4、备份主数据库
[root@ct-test1 ~]# mysqldump --password=Zxt1234! --all-databases --master-data > /mysql/dbdump.db
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@ct-test1 ~]# ls /mysql/dbdump.db
/mysql/dbdump.db
[root@ct-test1 ~]# ll /mysql/dbdump.db
-rw-r--r-- 1 root root 764914 Jun 24 17:59 /mysql/dbdump.db

    ok,主库的操作就完成了,下来备库,这里备库采用恢复主库的备份文件来实现,所以会省去一些步骤,比如创建同步用户等

    1、修改my.cnf,增加server-id
    我备库的my.cnf如下:
[root@ct-test1 soft]# cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/mysql/data
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
server-id=2

    2、完了后记得重启数据库
    [root@ct-test1 soft]# systemctl restart mysqld
    
    三、备库数据初始化
    
    这里用主库的备份来回灌数据库,主库的备份见上边/mysql/dbdump.db,这里只需要在主库上执行如下语句即可:
 [root@ct-test1 ~]# mysql --password=Zxt1234! -h 192.168.1.131 < /mysql/dbdump.db
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@ct-test1 ~]# 

    四、同步主、备关系
    在备库上执行如下命令:(这里mysql-bin.000002、154来源于主库SHOW MASTER STATUS的数据)
    mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.130', MASTER_USER='repl', MASTER_PASSWORD='Zxt1234!', MASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=154;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

    五、开启主从复制
    mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

    六、检查复制状态
    这个必须要说一下,Slave_IO_Running、Slave_SQL_Running同时为yes才说明同步环境搭建ok
    当然现实环境中(往往第一次搭建的时候会出很多问题),不会那么顺利,当不全为yes的时候:第一查看mysql.log日志,第二结合下边3个命令慢慢调:stop slave(停止复制)、reset slave(重置)、start slave

    mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.130
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 154
               Relay_Log_File: ct-test1-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 154
              Relay_Log_Space: 530
              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: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 101
                  Master_UUID: 3f93c491-39ee-11e6-8726-000c29916128
             Master_Info_File: /mysql/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

    ok,搭建完成,很简单吧,下边做一下同步测试
    我们在主库上执行建库、建表、插入数据等操作,然后从备库检查是否同步过去
    I、主库上的操作

    mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> create database zxt;
Query OK, 1 row affected (0.04 sec)

mysql> use zxt;
Database changed
mysql> create tables tab_test(id1 varchar(10));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'tables tab_test(id1 varchar(10))' at line 1
mysql> create table tab_test(id1 varchar(10));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into tab_test values ('12345');
Query OK, 1 row affected (0.01 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> quit

    II.备库上检查结果如下:
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zxt                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use zxt
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+---------------+
| Tables_in_zxt |
+---------------+
| tab_test      |
+---------------+
1 row in set (0.00 sec)

mysql> select * from tab_test;
+-------+
| id1   |
+-------+
| 12345 |
+-------+
1 row in set (0.00 sec)

mysql> 
阅读(100) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册