Mysql主从复制的实现

MySQL是一个开放源码的小型关联式数据库管理系统,开发者为瑞典MySQL AB公司。MySQL被广泛地应用在Internet上的中小型网站中。由于其体积小、速度快、总体拥有成本低,尤其是开放源码这一特点,许多中小型网站为了降低网站总体拥有成本而选择了MySQL作为网站数据库。Mysql内建的复制功能是构建大型、高性能应用程序的基础;将Mysql的数据分布到多个系统上,而这种分布的机制是通过将一台Mysql服务器的数据复制到其他主机(slave)上,由slave主机读取Master服务器的二进制日志文件然后重新在本地执行一遍来实现。做Mysql主从复制时,所有更新操作都只能在Master服务器,而Slave服务只负责更新自己的数据并提供查询操作。

Mysql复制主要能解决的有问题有:数据的分布、负载均衡、数据备份、高可用以及容错性。

Mysql的复制原理基本上可以分为以下3步:

1.在Master服务器将改变的数据记录到二进制日志(binary log)中(这些记录叫做二进制日志事件)

2.Slave服务器将Master服务器上的二进制日志拷贝到自己的中继日志(relay-log)中

3.Slave服务器读取中继日志中的事件,然后将改变的数据写入到自己的数据库中

 

第1步:在Master服务器上记录二进制日志。在每个更新数据的事务完成之前,Master服务器都会将数据更改记录到二进制日志中。即使事务在执行期间是交错的,Mysql也会串行地将事务写入到二进制日志中。在把事件写入二进制日志之后,Master服务器告诉存储引擎可以提交事务了

第2步:Slave服务器把主服务器的二进制日志拷贝到自己的硬盘上,进入所谓的“中继日志”中。首先,它启动一个工作线程,叫I/O线程,这个I/O线程开启一个普通的客户端连接,然后启动一个特殊的二进制日志转储进程(它没有相应的SQL命令)。这个转储进程Master服务器的二进制日志中读取数据。它不会对事件进行轮询。如果3跟上了Master服务器,就会进入休眠状态并等待有新的事件发生时Master服务器发出的信号。I/O线程把数据写入Slave服务器的中继日志中

第3步:SQL线程读取中继日志,并且重放其中的事件,然后更新Slave服务器的数据。由于这个线程能跟上I/O线程,中继日志通常在操作系统的缓存中,所以中继日志的开销很低。SQL线程执行事件也可以被写入Slave服务器自己的二进制日志中,它对于有些场景很实用

上图中显示了在Slave服务器有两个运行的线程,在Master服务器上也有一个运行的线程:和其他普通连接一样,由Slave服务器发起的连接,在Master服务器上同样拥有一个线程。

 

系统环境及拓扑

系统版本:CentOS 6.3_x86_64

Mysql版本:mysql-5.6.17

下载地址:http://cdn.mysql.com/Downloads/MySQL-5.6/mysql-5.6.17.tar.gz

 

1.准备工作

1.1修改master、slave主机名

master主机名:mysql_master

slave主机名:mysql_slave

1.2在master和slave上配置主机名解析

# cat >> /etc/hosts << EOF
172.16.10.72 mysql_master master
172.16.10.61 mysql_slave slave
EOF

1.3时间同步

# yum install -y ntpdate
# ntpdate ntp.fudan.edu.cn
# crontab -e
1 * * * * /usr/sbin/ntpdate ntp.fudan.edu.cn

2.安装Mysql(master与slave分别进行)

# groupadd mysql
# useradd -s /sbin/nologin -g mysql mysql
# tar xf mysql-5.6.17.tar.gz
# cd mysql-5.6.17# cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql
# make && make install# chown -R mysql.mysql /usr/local/mysql
# cd /usr/local/mysql/scripts/
# ./mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data #初始化mysql数据库,#指定mysql数据文件的存放位置
# cd ..
# cd support-files/# cp mysql.server /etc/rc.d/init.d/mysql
# cp my-default.cnf /etc/my.cnf
# chkconfig --add mysql
# chkconfig mysql on
# service mysql start
Starting MySQL..... SUCCESS!# ln -s /usr/local/mysql/bin/* /usr/bin/

cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql遇到报错如下:

- Could NOT find Curses (missing: CURSES_LIBRARY CURSES_INCLUDE_PATH)
CMake Error at cmake/readline.cmake:85 (MESSAGE):
  Curses library not found. Please install appropriate package,
      remove CMakeCache.txt and rerun cmake.On Debian/Ubuntu, package name is libncurses5-dev, on Redhat and derivates it is ncurses-devel.
Call Stack (most recent call first):
  cmake/readline.cmake:128 (FIND_CURSES)
  cmake/readline.cmake:202 (MYSQL_USE_BUNDLED_EDITLINE)
  CMakeLists.txt:411 (MYSQL_CHECK_EDITLINE)
-- Configuring incomplete, errors occurred!

解决办法:

yum -y install ncurses-devel

find / -name CMakeCache.txt|xargs rm -f 
然后重新执行cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql
 
3.配置主从复制
3.1在master上建立用于slave服务器复制数据的帐户
[root@mysql_master ~]# mysql
mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* to eivll0m@'172.16.10.61' IDENTIFIED by 'password'; 
Query OK, 0 rows affected (0.10 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.06 sec)
mysql> SHOW GRANTS FOR eivll0m@'172.16.10.61';
+---------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for eivll0m@172.16.10.61                                                                                                                   |
+---------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'eivll0m'@'172.16.10.61' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' |
+---------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)

3.2在Slave服务器上使用授权用户连接测试

[root@mysql_slave ~]# mysql -ueivll0m -ppassword -h 172.16.10.72 
Warning: Using a password on the command line interface can be insecure.
ERROR 2003 (HY000): Can't connect to MySQL server on '172.16.10.72' (113) #报错原因为master服务器iptables阻止了slave的连接,暂时先关闭即可

[root@mysql_slave ~]# mysql -ueivll0m -ppassword -h 172.16.10.72
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.17 Source distribution

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

3.3编辑master上的my.cnf,修改或增加以下内容:

basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
port = 3306
server_id = 1
socket = /tmp/mysql.sock
log-bin = mysql-bin
log_bin_index = mysql_bin.index
binlog_format = mixed
innodb_flush_log_at_trx_commit=1

重启mysqld服务

[root@mysql_master ~]# service mysqld restart

3.4编辑slave上的my.cnf,修改或增加以下内容:

basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
port = 3306
server_id = 2
socket = /tmp/mysql.sock
skip_slave_start = 1               
read_only = 1                     
relay_log = relay_log              
relay_log_index = relay_log.index  

重启mysqld服务

[root@mysql_slave ~]# service mysqld restart

3.5.查看master服务器的二进制日志及二进制日志事件位置

[root@mysql_master ~]# mysql -e 'SHOW MASTER STATUS;'
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      120 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
    File:表示从此日志开始复制
    Position:表示从这个事件开始复制

3.6在Slave服务器上同步Master服务器上面的数据

mysql> CHANGE MASTER TO MASTER_HOST='mysql_master',MASTER_USER='eivll0m',MASTER_PASSWORD='password',MASTER_PORT=3306,MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=120,MASTER_CONNECT_RETRY=60;

3.7启动slave服务器的复制线程并查看状态

mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: mysql_master
                  Master_User: eivll0m
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 120
               Relay_Log_File: relay_log.000002
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000003
             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: 120
              Relay_Log_Space: 450
              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: 1
                  Master_UUID: 988cd54d-c1a7-11e3-b1a5-000c29c976ef
             Master_Info_File: /usr/local/mysql/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           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
1 row in set (0.00 sec)

3.8在slave服务器查看启动的线程

[root@mysql_slave ~]# mysql -e 'SHOW PROCESSLIST;'
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
| Id | User        | Host      | db   | Command | Time | State                                                                       | Info             |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
|  5 | system user |           | NULL | Connect |  102 | Waiting for master to send event                                            | NULL             |
|  6 | system user |           | NULL | Connect |  102 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL             |
|  8 | root        | localhost | NULL | Query   |    0 | init                                                                        | SHOW PROCESSLIST |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+

3.9验证:在saster服务器创建数据库,在slave服务器上验证是否复制过去

[root@mysql_master ~]# mysql -e 'CREATE DATABASE eivll0m;' 
[root@mysql_master ~]# mysql -e 'SHOW DATABASES;' 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| eivll0m            |
| mysql              |
| performance_schema |
| test               |
+--------------------+
[root@mysql_slave ~]# mysql -e 'SHOW DATABASES;'     #可以看到eimll0m数据库已经复制过去
+--------------------+
| Database           |
+--------------------+
| information_schema |
| eivll0m            |
| mysql              |
| performance_schema |
| test               |
+--------------------+

3.10在主从服务器查看二进制日志事件位置是否更新

[root@mysql_master ~]# mysql -e 'SHOW MASTER STATUS;'
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      223 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
[root@mysql_slave ~]# mysql -e 'SHOW SLAVE STATUS\G;' | grep "Read_Master_Log_Pos" 
          Read_Master_Log_Pos: 223

由此可见,已经更新。

 

4.配置基于SSL的复制

由于Mysql的主从复制是明文传送的,如果在生产环境中跨网络使用主从还是明文传送,就无法保证数据的传输安全性,为了解决这一问题,我们需要加密进行传送,也就是基于SSL的加密方法进行传输数据。

4.1在master服务器搭建CA服务器

[root@mysql_master ~]# cd /etc/pki/CA/
[root@mysql_master CA]# (umask 077;openssl genrsa -out private/cakey.pem 2048)
[root@mysql_master CA]# openssl req -new -x509 -key private/cakey.pem -out cacert.pem -days 365
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [XX]:CN
State or Province Name (full name) []:BeiJing
Locality Name (eg, city) [Default City]:ChaoYang
Organization Name (eg, company) [Default Company Ltd]:eivll0m
Organizational Unit Name (eg, section) []:Tech
Common Name (eg, your name or your server's hostname) []:mysql_master
Email Address []:master@eivll0m.com
[root@mysql_master CA]# touch index.txt
[root@mysql_master CA]# echo 01 > serial

4.2为master创建证书申请并由CA服务器签发证书

[root@mysql_master ~]# mkdir /usr/local/mysql/ssl
[root@mysql_master ssl]# cd /usr/local/mysql/ssl
[root@mysql_master ssl]# (umask 077;openssl genrsa -out master.key 2048)
[root@mysql_master ssl]# openssl req -new -key master.key -out master.csr -days 365
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [XX]:CN
State or Province Name (full name) []:BeiJing
Locality Name (eg, city) [Default City]:ChaoYang
Organization Name (eg, company) [Default Company Ltd]:eivll0m
Organizational Unit Name (eg, section) []:Tech
Common Name (eg, your name or your server's hostname) []:mysql_master
Email Address []:master@eivll0m.com

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
[root@mysql_master ssl]# openssl ca -in master.csr -out master.crt -days 365
Using configuration from /etc/pki/tls/openssl.cnf
Check that the request matches the signature
Signature ok
Certificate Details:
        Serial Number: 1 (0x1)
        Validity
            Not Before: Apr 22 15:52:49 2014 GMT
            Not After : Apr 22 15:52:49 2015 GMT
        Subject:
            countryName = CN
            stateOrProvinceName = BeiJing
            organizationName = eivll0m
            organizationalUnitName = Tech
            commonName = mysql_master
            emailAddress = master@eivll0m.com
        X509v3 extensions:
            X509v3 Basic Constraints: 
                CA:FALSE
            Netscape Comment: 
                OpenSSL Generated Certificate
            X509v3 Subject Key Identifier: 
                A7:4D:33:91:61:CD:92:5E:72:2A:8E:A6:56:15:6A:AB:FA:22:20:98
            X509v3 Authority Key Identifier: 
                keyid:0F:79:D1:B8:1C:63:4B:91:A6:17:9F:B4:6D:A3:C7:96:AA:29:5E:48

Certificate is to be certified until Apr 22 15:52:49 2015 GMT (365 days)
Sign the certificate? [y/n]:y

1 out of 1 certificate requests certified, commit? [y/n]y
Write out database with 1 new entries
Data Base Updated

4.3为slave服务器创建证书申请

[root@mysql_slave ~]# mkdir /usr/local/mysql/ssl
[root@mysql_slave ~]# cd /usr/local/mysql/ssl
[root@mysql_slave ~]# (umask 077;openssl genrsa -out slave.key 2048)
[root@mysql_slave ssl]# openssl req -new -key slave.key -out slave.csr -days 365
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [XX]:CN
State or Province Name (full name) []:BeiJing
Locality Name (eg, city) [Default City]:ChaoYang
Organization Name (eg, company) [Default Company Ltd]:eivll0m
Organizational Unit Name (eg, section) []:Tech
Common Name (eg, your name or your server's hostname) []:mysql_slave
Email Address []:

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:

4.4为slave服务器签署证书

[root@mysql_slave ssl]# scp slave.csr mysql_master:/tmp/
[root@mysql_master ssl]# openssl ca -in /tmp/slave.csr -out /tmp/slave.crt -days 365
Using configuration from /etc/pki/tls/openssl.cnf
Check that the request matches the signature
Signature ok
Certificate Details:
        Serial Number: 2 (0x2)
        Validity
            Not Before: Apr 22 15:57:52 2014 GMT
            Not After : Apr 22 15:57:52 2015 GMT
        Subject:
            countryName = CN
            stateOrProvinceName = BeiJing
            organizationName = eivll0m
            organizationalUnitName = Tech
            commonName = mysql_slave
            emailAddress = slave@eivll0m.com
        X509v3 extensions:
            X509v3 Basic Constraints: 
                CA:FALSE
            Netscape Comment: 
                OpenSSL Generated Certificate
            X509v3 Subject Key Identifier: 
                B9:4B:EA:28:0E:9E:4B:84:A6:9A:4E:45:3B:DF:B3:B9:E3:E9:ED:55
            X509v3 Authority Key Identifier: 
                keyid:0F:79:D1:B8:1C:63:4B:91:A6:17:9F:B4:6D:A3:C7:96:AA:29:5E:48

Certificate is to be certified until Apr 22 15:57:52 2015 GMT (365 days)
Sign the certificate? [y/n]:y

1 out of 1 certificate requests certified, commit? [y/n]y
Write out database with 1 new entries
Data Base Updated

在mastet服务器上将签署好证书申请拷贝到Slave服务器

[root@mysql_master ~]# scp /tmp/slave.crt mysql_slave:/usr/local/mysql/ssl/

4.5将CA证书拷贝到slave服务器与saster相应目录

[root@mysql_master ~]# scp /etc/pki/CA/cacert.pem mysql_slave:/usr/local/mysql/ssl/
[root@mysql_master ~]# cp /etc/pki/CA/cacert.pem /usr/local/mysql/ssl/

4.6修改master与slave服务器证书属主、属组为"mysql"用户

# chown -R mysql.mysql /usr/local/mysql/ssl
# ll /usr/local/mysql/ssl/
-rw-r--r-- 1 mysql mysql 1415 Sep 20 20:57 cacert.pem
-rw-r--r-- 1 mysql mysql 4600 Sep 20 20:22 master.crt
-rw-r--r-- 1 mysql mysql 1054 Sep 20 20:20 master.csr
-rw------- 1 mysql mysql 1675 Sep 20 20:17 master.key

4.7在master与slave服务器编辑my.cnf开启SSL加密功能

在master服务器的my.cnf文件中[mysqld]下添加如下参数

ssl                                               #开启SSL功能
ssl_ca = /usr/local/mysql/ssl/cacert.pem          #指定CA文件位置
ssl_cert = /usr/local/mysql/ssl/master.crt  #指定证书文件位置
ssl_key = /usr/local/mysql/ssl/master.key   #指定密钥所在位置

在slave服务器的my.cnf文件中[mysqld]下添加如下参数

ssl
ssl_ca = /usr/local/mysql/ssl/cacert.pem
ssl_cert = /usr/local/mysql/ssl/slave.crt
ssl_key = /usr/local/mysql/ssl/slave.key

4.8在master服务器查看SSL加密是否开启并创建授权一个基于密钥认证的用户

mysql> SHOW VARIABLES LIKE '%ssl%';
+---------------+---------------------------------+
| Variable_name | Value                           |
+---------------+---------------------------------+
| have_openssl  | YES                             |
| have_ssl      | YES                             |
| ssl_ca        | /usr/local/mysql/ssl/cacert.pem |
| ssl_capath    |                                 |
| ssl_cert      | /usr/local/mysql/ssl/master.crt |
| ssl_cipher    |                                 |
| ssl_crl       |                                 |
| ssl_crlpath   |                                 |
| ssl_key       | /usr/local/mysql/ssl/master.key |
+---------------+---------------------------------+
9 rows in set (0.12 sec)
mysql> GRANT REPLICATION CLIENT,REPLICATION SLAVE ON *.* to 'slave'@'172.16.%.%' IDENTIFIED BY 'passwd' REQUIRE SSL;
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.02 sec)

4.9查看master服务器二进制日志文件和事件位置

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000007 |      919 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.09 sec)

4.10在slave上测试使用加密用户指定密钥连接master服务器(如下测试成功)

root@mysql_slave ssl]# mysql -uslave -ppasswd -h 172.16.10.72 --ssl-ca=/usr/local/mysql/ssl/cacert.pem --ssl-cert=/usr/local/mysql/ssl/slave.crt --ssl-key=/usr/local/mysql/ssl/slave.key
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.17-log Source distribution

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

4.11查看slave服务器SSL是否开启并连接master服务器

mysql> SHOW VARIABLES LIKE '%ssl%';
+---------------+---------------------------------+
| Variable_name | Value                           |
+---------------+---------------------------------+
| have_openssl  | YES                             |
| have_ssl      | YES                             |
| ssl_ca        | /usr/local/mysql/ssl/cacert.pem |
| ssl_capath    |                                 |
| ssl_cert      | /usr/local/mysql/ssl/master.crt |
| ssl_cipher    |                                 |
| ssl_crl       |                                 |
| ssl_crlpath   |                                 |
| ssl_key       | /usr/local/mysql/ssl/master.key |
+---------------+---------------------------------+
mysql> change master to master_host='172.16.10.72',master_user='slave',master_password='passwd',master_log_file='mysql-bin.000007',master_log_pos=919,master_ssl=1,master_ssl_ca='/usr/local/mysql/ssl/cacert.pem',master_ssl_cert='/usr/local/mysql/ssl/slave.crt',master_ssl_key='/usr/local/mysql/ssl/slave.key';
mysql> start slave;     #启动IO线程
mysql> show slave status\G; ##查看slave状态 

4.12查看slave服务器状态

[root@mysql_slave ~]# cd /usr/local/mysql/ssl/
[root@mysql_slave ssl]# mysql -e 'show slave status\G;'
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.10.72
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000007
          Read_Master_Log_Pos: 919
               Relay_Log_File: relay_log.000002
                Relay_Log_Pos: 572
        Relay_Master_Log_File: mysql-bin.000007
             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: 919
              Relay_Log_Space: 739
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: Yes
           Master_SSL_CA_File: /usr/local/mysql/ssl/cacert.pem
           Master_SSL_CA_Path: 
              Master_SSL_Cert: /usr/local/mysql/ssl/slave.crt
            Master_SSL_Cipher: 
               Master_SSL_Key: /usr/local/mysql/ssl/slave.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: 1
                  Master_UUID: 988cd54d-c1a7-11e3-b1a5-000c29c976ef
             Master_Info_File: /usr/local/mysql/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           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

4.13创建数据库进行验证

[root@mysql_master ssl]# mysql -e 'create database mydata'   
[root@mysql_master ssl]# mysql -e 'show databases' 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| eivll0m            |
| mydata             |
| mysql              |
| performance_schema |
| test               |
+--------------------+
[root@mysql_slave data]# mysql -e 'show databases;'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| eivll0m            |
| mydata             |
| mysql              |
| performance_schema |
| test               |
+--------------------+

复制成功!

 

 

 

 

 

posted on 2014-06-08 17:50  K‘e0llm  阅读(541)  评论(0编辑  收藏  举报

导航