mysql主从复制实践之单数据库多实例

1.主从复制数据库实战环境准备

       MySQL主从复制实践对环境的要求比较简单，可以是单机单数据库多实例的环境，也可以是两台服务器之间，每台服务器都部署一个独立的数据库的环境。本文以单机数据库多实例的环境进行实践。

2.主从复制服务器角色定义

[root@bogon bin]# netstat -lntp | grep 330

tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      24215/mysqld        

tcp        0      0 0.0.0.0:3307                0.0.0.0:*                   LISTEN      24521/mysqld        

tcp        0      0 0.0.0.0:3308                0.0.0.0:*                   LISTEN      24806/mysqld 

3.在主库master上执行如下配置操作
3.1.设置server-id值并开启binlog功能参数

       由MySQL主从复制原理我们知道，要实现主从复制，关键是要开启binlog日志功能，所以，要打开主库的binlog日志参数。

3.1.1修改主库的配置文件/data/3306/my.cnf，执行vim /data/3306/my.cnf，按如下内容修改参数：

[mysqld]

server-id = 1                        #用于同步的实例server-id 都不能相同

log-bin = /data/3306/mysql-bin             #开启binlog日志功能

3.1.2检查修改后的参数：

[root@bogon mysql]# grep -E "server-id|log-bin" /data/3306/my.cnf 

log-bin = /data/3306/mysql-bin

server-id = 1

3.1.3重启主库mysql服务

[root@bogon mysql]# /data/3306/mysql restart

3.1.4登录数据库，检查参数修改情况：

mysql> show variables like 'server_id';

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

| Variable_name | Value |

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

| server_id     | 1     |

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

1 row in set (0.00 sec)

mysql> show variables like 'log_bin';

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

| Variable_name | Value |

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

| log_bin       | ON    |

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

1 row in set (0.00 sec)

经检测，主库的binlog功能以及开启；

3.2在主库上建立用于主从复制的账号

       由主从复制的原理可知，从库要和主库同步，需有一个可以连接主库的账号，并且这个账号是在主库上建立的，权限允许从库连接主库并同步数据。

3.2.1在主库创建账号rep,具体操作如下：

mysql -uroot -p -S /data/3306/mysql.sock         #登录数据库；

mysql> grant replication slave on *.* to 'rep'@'192.168.1.%' identified by 'lb123456';  #创建rep账号；

3.2.2检查账号创建情况

mysql> use mysql;

mysql> select user,host from user where user='rep';

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

| user | host        |

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

| rep  | 192.168.1.% |

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

1 row in set (0.00 sec)

3.3在主库上锁表全备数据库，查看并记录主库状态信息；

3.3.1对主数据库进行锁表操作；

mysql> flush tables with read lock;

Query OK, 0 rows affected (0.01 sec)

3.3.2锁表后查看主库状态

mysql> show master status;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000004 |      259 |              |                  |

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

1 row in set (0.00 sec)

注意：需记录该命令显示的信息，后续从库需用到此信息；

3.3.3对主库数据进行全备，具体操作如下：

[root@bogon /]# mkdir -p /server/backup

[root@bogon /]# mysqldump -uroot -S /data/3306/mysql.sock  --events -A -B | gzip > /server/backup/mysql_bak_$(date +%F).sql.gz

[root@bogon /]# cd /server/backup

[root@bogon backup]# ll

total 144

-rw-r--r--. 1 root root 144803 Jun 14 14:59 mysql_bak_2016-06-14.sql.gz

3.3.4再次查看主库状态信息，确保数据导出期间，数据库没有数据变更；

[root@bogon backup]# mysql -uroot -S /data/3306/mysql.sock -e "show master status";

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000004 |      259 |              |                  |

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

注意：binlog文件及位置点如果前后一致，即没有变化，则说明主库数据库在锁表导出数据前后数据库没有数据变更；

3.3.5解锁主库，恢复可写；

mysql> unlock tables;

Query OK, 0 rows affected (0.00 sec)

4.在从库slave1上执行如下配置操作

4.1.设置server-id值并关闭binlog功能参数

这里从库的server-id要和主库及其他从库的不同，并且注释从库的binlog参数配置

4.1.1修改主库的配置文件/data/3307/my.cnf，执行vim /data/3307/my.cnf，按如下内容修改参数：

[mysqld]

server-id = 2                        #用于同步的实例server-id 都不能相同

#log-bin = /data/3306/mysql-bin             #关闭binlog日志功能

4.1.2检查修改后的参数：

[root@bogon backup]# grep -E "server-id|log-bin" /data/3307/my.cnf

#log-bin = /data/3307/mysql-bin

server-id = 2

4.1.3重启主库mysql服务

[root@bogon mysql]# /data/3307/mysql restart

4.1.4登录数据库，检查参数修改情况：

mysql> show variables like 'server_id';

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

| Variable_name | Value |

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

| server_id     | 2     |

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

1 row in set (0.00 sec)

mysql> show variables like 'log_bin';

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

| Variable_name | Value |

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

| log_bin       | OFF   |

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

1 row in set (0.00 sec)

4.2把主库导出的数据恢复到从库slave1上；

[root@bogon /]# cd /server/backup/        #进入备份目录；

[root@bogon backup]# ll

total 144

-rw-r--r--. 1 root root 144803 Jun 14 14:59 mysql_bak_2016-06-14.sql.gz

[root@bogon backup]# gzip -d mysql_bak_2016-06-14.sql.gz      #解压备份数据库数据

[root@bogon backup]# ll

total 516

-rw-r--r--. 1 root root 527436 Jun 14 14:59 mysql_bak_2016-06-14.sql

[root@bogon backup]# mysql -uroot -S /data/3307/mysql.sock

4.3登录从库slave1，配置复制参数

mysql> change master to 

    -> master_host='192.168.1.20',

    -> master_port=3306,

    -> master_user='rep',

    -> master_password='lb123456',

    -> master_log_file='mysql-bin.000004',

    -> master_log_pos=259;

Query OK, 0 rows affected (0.16 sec)

4.4启动从库同步开关，查看复制状态

mysql> start slave;

Query OK, 0 rows affected (0.04 sec)

主从复制是否成功，最关键为下面3项参数：

[root@bogon backup]# mysql -uroot -S /data/3307/mysql.sock -e "show slave status\G" | grep -E "IO_Running|SQL_Running|Seconds_Behind_Master"

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

        Seconds_Behind_Master: 0

4.5测试主从复制结果

在主库创建新的数据库，然后观察从库的数据状况。

[root@bogon backup]# mysql -uroot -S /data/3306/mysql.sock -e "create database langyabang";   #在主库上创建新的数据库‘langyabang’;

[root@bogon backup]# mysql -uroot -S /data/3306/mysql.sock -e "show databases like 'langyabang'";

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

| Database (langyabang) |

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

| langyabang            |

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

#在主库上查看数据库‘langyabang’创建情况；

[root@bogon backup]# mysql -uroot -S /data/3307/mysql.sock -e "show databases like 'langyabang'";

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

| Database (langyabang) |

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

| langyabang            |

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

#在从库上查看主从复制情况；

根据测试主从是同步的。