Mysql

Mysql主从复制数据库同步

mysql主从同步定义:

主从同步使数据可以用一个数据库复制到另外一个数据库上,在复制数据时其中一台服务器充当主服务器master,其余服务器充当从服务器slave。通过配置文件可指定复制数据库、或者是数据库上的某个表。

主从同步的优点:

1.提高数据的安全性--数据备份

2.水平扩展数据库的负载能力

3.高可用性和容错

 

mysql复制如何工作:

1.master将改变记录到二进制日志(binary log)中(这些记录叫做二进制日志事件,binary log events);

2.slave将master的binary log events拷贝到它的中继日志(relay log);

3.slave重做中继日志中的事件,将改变反映它自己的数据

下图表述了mysql主从复制的过程

 

配置主从同步

环境:

主服务器:centos 6.5 10.10.10.10    mysql-5.1.73

从服务器:centos6.5  10.10.10.20    mysql-5.1.73

 

主服务器:

yum安装mysql

[root@i-0nonjni0 ~]#yum install  mysql mysql-devel mysql-server

启动mysql

[root@i-0nonjni0 ~]#service mysqld start

设置mysql root密码

[root@i-0nonjni0 ~]#mysqladmin -u root  password  www.huangfh.cn

登录mysql创建一个复制用户,具有replication slave 权限

mysql>grant replication slave on *.* to '51idc'@'10.10.10.20' identified by '51idc';

刷新策略

mysql>flush privileges;

编辑my.cnf

添加server-id=10

log-bin=/var/lib/mysql/mysql-bin

[mysqld]
server-id=10
log-bin=/var/lib/mysql/mysql-bin

重启mysql数据库

[root@i-0nonjni0 ~]#service  mysqld restart

设置读锁

mysql>flush tables with read lock;

得到binlog日志文件名和偏移量

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

备份要同步的数据库

mysqldump -u 用户名 -p 数据库名 > 导出的文件名
[root@i-0nonjni0 ~]#mysqldump -u root -p wordpress > wordpress.sql

解锁

mysql>unlock tables;

 

 

从服务器:

yum安装mysql

[root@i-0nonjni0 ~]#yum install  mysql mysql-devel mysql-server

编辑my.cnf

添加server-id=20

log-bin=/var/lib/mysql/mysql-bin

[mysqld]
server-id=20        #指定server唯一ID
log-bin=/var/lib/mysql/mysql-bin  
relay_log=mysql-relay-bin   #配置中继日志
log_slave_updates=1        #表示slave将复制事件写进自己的二进制日志
read_only=1                #防止改变数据

重启从数据库

[root@i-0nonjni0 ~]#service  mysqld restart

配置同步信息

mysql> stop slave;
mysql> change master to 
    -> master_host='10.10.10.10',
    -> master_user='51idc',
    -> master_password='51idc',
    -> master_log_file='mysql-bin.000001',
    -> master_log_pos=199;
Query OK, 0 rows affected (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.10.10.10      #当前主服务器主机IP
                  Master_User: 51idc            #被用于连接主服务器的用户
                  Master_Port: 3306             #当前主服务器端口
                Connect_Retry: 60               #选项的当前值
              Master_Log_File: mysql-bin.000001   #SLAVE中的I/O线程当前正在读取的主服务器二进制日志文件的名称
          Read_Master_Log_Pos: 199              # 在当前的主服务器二进制日志中,SLAVE中的I/O线程已经读取的位置
               Relay_Log_File: mysql-relay-bin.000002  #SQL线程当前正在读取和执行的中继日志文件的名称
                Relay_Log_Pos: 251          #在当前的中继日志中,SQL线程已读取和执行的位置
        Relay_Master_Log_File: mysql-bin.000001  #由SQL线程执行的包含多数近期事件的主服务器二进制日志文件的名称
             Slave_IO_Running: Yes           #I/O线程是否被启动并成功地连接到主服务器上  
            Slave_SQL_Running: Yes           #SQL线程是否被启动
              Replicate_Do_DB:               #replicate-do-db选项的当前值
          Replicate_Ignore_DB:               #replicate-ignore-db选项的当前值
           Replicate_Do_Table:               #replicate-do-table选项的当前值
       Replicate_Ignore_Table:               #replicate-ignore-table选项的当前值
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0      #最近一次错误码
                   Last_Error:        #最近一次错误内容
                 Skip_Counter: 0      #最近被使用的用于SQL_SLAVE_SKIP_COUNTER的值
          Exec_Master_Log_Pos: 199    #来自主服务器的二进制日志的由SQL线程执行的上一个时间的位置
              Relay_Log_Space: 406    #所有原有的中继日志结合起来的总大小
              Until_Condition: None   
               Until_Log_File:       #用于指示日志文件名,日志文件名和位置值定义了SQL线程在哪个点中止执行
                Until_Log_Pos: 0     #用于指示日志位置值,日志文件名和位置值定义了SQL线程在哪个点中止执行
           Master_SSL_Allowed: No    #如果允许对主服务器进行SSL连接,则值为Yes。如果不允许对主服务器进行SSL连接,则值为No。如果允许SSL连接,但是从属服务器没有让SSL支持被启用,则值为Ignored。
           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: 
1 row in set (0.00 sec)

测试:

配置完以后可查看master和slave上线程的状态。在master上,可以看到slave的I/O线程创建的连接:master上输入show processlist\G;

mysql> show processlist\G;
*************************** 1. row ***************************
     Id: 37
   User: root
   Host: localhost
     db: NULL
Command: Query
   Time: 0
  State: NULL
   Info: show processlist
*************************** 2. row ***************************
     Id: 38
   User: 51idc
   Host: 10.10.10.20:51549
     db: NULL
Command: Binlog Dump
   Time: 82
  State: Has sent all binlog to slave; waiting for binlog to be updated
   Info: NULL
2 rows in set (0.00 sec)

在主数据库添加数据或创建数据库

mysql> create database  111test;
Query OK, 1 row affected (0.00 sec)
mysql> show  databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| 111test            |
| mysql              |
| test               |
| wordpress          |
+--------------------+
5 rows in set (0.00 sec)

从数据库查看:

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

 

(0)

本文由 樱花博客 作者:樱花 发表,转载请注明来源!

关键词:, ,

热评文章

发表评论