MySQL5.6主从复制方案

MySQL5.6主从复制方案

1.主备服务器操作

环境：CentOS 6.3/6.4 最小化缺省安装，配置好网卡。

安装MySQL前，确认Internet连接正常，以便下载安装文件。

＃ 新增用户组
groupadd mysql

＃ 新增用户
useradd mysql -g mysql

＃ 新建数据库执行文件目录
mkdir -p /usr/local/mysql

＃ 新建数据库数据文件目录
mkdir -p /db/mysql/data

＃ 编辑PATH搜索路径
vi /etc/profile
PATH=/usr/local/mysql/bin:/usr/local/mysql/lib:$PATH
export PATH

＃ 生效PATH搜索路径
source /etc/profile

＃ 安装编译源码所需的工具和库
yum -y install wget gcc-c++ ncurses-devel cmake make perl

＃ 进入源码压缩包下载目录
cd /usr/local/src

＃ 下载源码压缩包
wget http://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.10.tar.gz/from/http://cdn.mysql.com/

＃ 解压缩源码包
tar -zxvf mysql-5.6.10.tar.gz

＃ 进入解压缩源码目录
cd mysql-5.6.10

＃ 从mysql5.5起，mysql源码安装开始使用cmake了，执行源码编译配置脚本。

cmake \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_MYISAM_STORAGE_ENGINE=1 \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_MEMORY_STORAGE_ENGINE=1 \
-DWITH_READLINE=1 \
-DENABLED_LOCAL_INFILE=1 \
-DMYSQL_DATADIR=/db/mysql/data \
-DMYSQL_USER=mysql \
-DMYSQL_TCP_PORT=3306

＃ 编译源码，这一步时间会较长，耐心等待。
make

＃ 安装
make install

＃ 清除安装临时文件
make clean

＃ 修改目录拥有者
chown -R mysql:mysql /usr/local/mysql
chown -R mysql:mysql /db/mysql/data

＃ 进入安装路径
cd /usr/local/mysql

＃ 执行初始化配置脚本，创建系统自带的数据库和表。
scripts/mysql_install_db --user=mysql --datadir=/db/mysql/data
初始化脚本在 /usr/local/mysql/my.cnf 生成了配置文件。需要更改该配置文件的所有者：
chown -R mysql:mysql /usr/local/mysql
mv /etc/my.cnf /etc/my.cnf.bak

＃ 复制服务启动脚本
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql

＃ 启动MySQL服务
service mysql start

# 设置开机自动启动服务
chkconfig mysql on

# 修改MySQL用户root的密码
mysql -u root

mysql>use mysql;
mysql>GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY "root";
mysql>update user set Password = password(‘123456‘) where User=‘root‘;
mysql>flush privileges;
mysql>exit;

移除test数据库和匿名用户，强烈建议生产服务器使用：

/usr/local/mysql/bin/mysql_secure_installation

2.修改主服务器master:

vi /usr/local/mysql/my.cnf

[mysqld]

log-bin=mysql-bin

server-id=1

3.修改从服务器slave:

vi /usr/local/mysql/my.cnf

[mysqld]

log-bin=mysql-bin

server-id=2

4.重启两台服务器的MySQL

service mysql restart

5.在主服务器上建立账户并授权slave:

[root@cd2 mysql]# mysql -u root –p

mysql> grant replication slave on *.* to repl@‘%‘ identified by ‘replica‘;

Query OK, 0 rows affected (0.00 sec)

6.登录主服务器的MySQL，查询master的状态并记录master的复制坐标(从库用改点的坐标开始从主服务器上进行更新)

mysql> show master status;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| mysql-bin.000001 |      318 |              |                  |                   |

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

1 row in set (0.00 sec)

7.配置从服务器slave

[root@cd3 mysql]# mysql -u root –p

mysql> change master to

    -> master_host=‘192.168.100.185‘,

    -> master_user=‘repl‘,

    -> master_password=‘replica‘,

    -> master_log_file=‘mysql-bin.000001‘,

    -> master_log_pos=318;

Query OK, 0 rows affected, 2 warnings (0.27 sec)

mysql> start slave;

Query OK, 0 rows affected (0.03 sec)

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

mysql> show slave status\G

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

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.100.185

                  Master_User: repl

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000001

          Read_Master_Log_Pos: 318

               Relay_Log_File: cd3-relay-bin.000002

                Relay_Log_Pos: 283

        Relay_Master_Log_File: mysql-bin.000001

             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: 318

              Relay_Log_Space: 454

              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: 583d568a-094e-11e5-8a35-00163eef6712

             Master_Info_File: /db/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)

其中Slave_IO和Slave_SQL进程必须正常，即状态为Yes，否则都是错误的状态(有一个为NO就为错误)

9.主从服务器测试

在主库中创建数据库

mysql> create database student_db;

Query OK, 1 row affected (0.00 sec)

在mysql中插入数据

mysql> use student_db;

Database changed

mysql> create table student_tb(std_id char(20),std_name char(20),std_sex char(1),std_class,char(1));

mysql> insert into student_tb values(‘0000001‘,‘li hua‘,‘M‘,‘1‘);

Query OK, 1 row affected (0.03 sec)

mysql> insert into student_tb values(‘0000002‘,‘li shan‘,‘F‘,‘2‘);

Query OK, 1 row affected (0.02 sec)

mysql> insert into student_tb values(‘0000003‘,‘song qi‘,‘F‘,‘3‘);

Query OK, 1 row affected (0.02 sec)

mysql> select * from student_db.student_tb;

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

| std_id  | std_name | std_sex | std_class |

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

| 0000001 | li hua   | M       | 1         |

| 0000002 | li shan  | F       | 2         |

| 0000003 | song qi  | F       | 3         |

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

3 rows in set (0.00 sec)

mysql> show master status;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| mysql-bin.000001 |     1405 |              |                  |                   |

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

1 row in set (0.00 sec)

从服务器MySQL查询

mysql> show databases;

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

| Database           |

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

| information_schema |

| mysql              |

| performance_schema |

| student_db         |

| test               |

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

5 rows in set (0.00 sec)

mysql> use student_db;

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_student_db |

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

| student_tb           |

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

1 row in set (0.00 sec)

mysql> select * from student_tb;

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

| std_id  | std_name | std_sex | std_class |

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

| 0000001 | li hua   | M       | 1         |

| 0000002 | li shan  | F       | 2         |

| 0000003 | song qi  | F       | 3         |

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

3 rows in set (0.00 sec)

mysql> show slave status\G

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

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.100.185

                  Master_User: repl

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000001

          Read_Master_Log_Pos: 1405

               Relay_Log_File: cd3-relay-bin.000002

                Relay_Log_Pos: 1370

        Relay_Master_Log_File: mysql-bin.000001

             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: 1405

              Relay_Log_Space: 1541

              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: 583d568a-094e-11e5-8a35-00163eef6712

             Master_Info_File: /db/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)