mysql主从数据库配置文件_Mysql主从数据库配置

1. 概述

1.1 目标

解决web应用系统，数据库出现的性能瓶颈，采用数据库集群的方式来实现查询负载；一个系统中数据库的查询操作比更新操作要多得多，通过多台查询服务器将数据库的查询分担到不同的查询服务器上从而提高查询效率。

Mysql数据库支持数据库的主从复制功能，使用主数据库进行数据的插入、删除与更新操作，而从数据库则专门用来进行数据查询操作，这样可以将更新操作和查询操作分担到不同的数据库上，从而提高了查询效率。

1.2 Mysql主从复制原理

主服务器上面的任何修改会保存在二进制日志文件Binary log里，从服务器上启动一个I/O thread进程与主服务器的I/O联系，并请求从指定日志文件的位置之后的内容。当主服务器接收到Slave服务器I/O线程请求后，通过I/O线程根据请求信息读取指定日志指定位置之后的日志信息，返回给Slave服务器的I/O线程。在返回的信息中除了日志所包含的信息之外，还有master端的binary log文件的名称和binary log中的位置。然后把读取到的二进制日志内容写到本地的一个Realy log里面，将读取到的master端的bin-log文件名和位置记录到master-info文件中。从服务器上面开启一个SQL thread定时检查Realy log，如果发现有更改立即把更改的内容在本机上面执行一遍。这样就实现了在主服务器上操作，从服务器上实时也跟着操作。

1.3 网络拓扑

2. 主数据配置

2.1 启动log-bin

[root@master ~]# vim /etc/my.cnf

[mysqld]

log-bin=/data/mysql/binlog/binlog

server-id=101

binlog-format=mixed

备注：开启log-bin日志记录，设置唯一的server id,记录日志的模式

查看log-bin

[root@testdb2 bin_log]# ls -l

total 8

-rw-rw---- 1 mysql mysql 120 Apr 30 18:33 bin-log.000001

-rw-rw---- 1 mysql mysql  39 Apr 30 18:33 bin-log.index

mysql> show variables like 'log_bin%';

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

| Variable_name                   | Value                                 |

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

| log_bin                         | ON                                  |

| log_bin_basename                | /data/3307/data/bin_log/bin-log       |

| log_bin_index                   | /data/3307/data/bin_log/bin-log.index |

| log_bin_trust_function_creators | OFF                                   |

| log_bin_use_v1_row_events       | OFF                                   |

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

5 rows in set (0.00 sec)

2.2 创建授权账号

mysql>grant replication client,replication slave on *.*

to 'repl'@'192.168.150.%'

identified by 'repl';

mysql> flush privileges;

mysql> select user,host,password from mysql.user;

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

| user | host          | password                                  |

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

| root | localhost     |                                           |

| root | testdb2       |                                           |

| root | 127.0.0.1     |                                           |

| root | ::1           |                                           |

|      | localhost     |                                           |

|      | testdb2       |                                           |

| 'repl'| 192.168.150.% | *9FF2C222F44C7BBA5CC7E3BE8573AA4E1776278C |

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

7 rows in set (0.00 sec)

查看主数据库状态

mysql> show master status;

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

| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| binlog.000009 |      581 |              |                  |                   |

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

1 row in set (0.00 sec)

mysql> show master logs;

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

| Log_name       | File_size |

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

| bin-log.000009|       581 |

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

1 row in set (0.00 sec)

2.3 备份数据库

锁表

mysql> flush tables with read lock;

Query OK, 0 rows affected (0.00 sec)

备份数据，同时记录下File和position的值

备份所有数据库

[root@testdb2 dump]# mysqldump -uroot -p -S /data/3307/mysql.sock -A -B --master-data=1 --events > full_bak_3307.sql

参数解释：

-A：全数据库备份

-B：备份脚本有创建数据库语句，例如 create database test; use test;

--master-data=1 表示会执行 CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000015', MASTER_LOG_POS=120; 这个语句，所有在从库段不要添加这两个参数 master_log_file，master_log_pos

--master-data=2 表示会注释 --CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000015', MASTER_LOG_POS=120; 这个语句

数据库文件比较大可以压缩备份

[root@testdb2 dump]# mysqldump -uroot -p -S /data/3307/mysql.sock -A -B --master-data=1 --events |gzip > full_bak_3307.sql.gz

备份完成解锁

mysql> unlock tables;

Query OK, 0 rows affected (0.00 sec)

3. 从数据库配置

3.1 导入所有数据库

[root@testdb2 dump]# mysql -uroot -p -S /data/3308/mysql.sock < full_bak_3307.sql

3.2 启动replaylog

[root@slave ~]# vim /etc/my.cnf

[mysqld]

server-id=111

relay-log=/data/mysql/relaylog/relaylog

[root@slave ~]# service mysqld restart

切换到主数据库

mysql> change master to

master_host='192.168.150.101',

master_port=3306,

master_user='repl',

master_password='repl',

master_log_file='binlog.000009',

master_log_pos=581;

备注：mysqldump 语句中有 --master-data=1 不需要添加，--master-data=2时需要添加。

多实例数据库制定不同的端口

change master to

master_host='192.168.150.111',

master_port=3307,

master_user='rep',

master_password='rep';

3.3 启动从数据库

[root@slave ~]#

mysql>start slave;

mysql> show slave status \G;         ß查看mysql主从同步是否正常

mysql> show slave status \G

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

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.150.101

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: binlog.000009

Read_Master_Log_Pos: 581

Relay_Log_File: relaylog.000006

Relay_Log_Pos: 741

Relay_Master_Log_File: binlog.000009

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

Relay_Log_Space: 1067

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: 4c469e17-b9bf-11e5-88c0-000c29f82eaa

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

备注：如果报错

mysql> start slave;

ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository

需要重置 slave 查看 Master_Info_File: /data/3308/data/master.info 是否正确

mysql> reset slave;

Query OK, 0 rows affected (0.00 sec)

IO记录日志 master.info

SQL记录的只 relay-log.info

4. 验证MySQL主从

4.1 主服务器创建数据库

mysql> create database db3;

Query OK, 1 row affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

从服务器查看

mysql> show databases;

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

| Database           |

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

| information_schema |

| db2                |

| db3               |

| mysql              |

| performance_schema |

| relaylog           |

| test               |

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

7 rows in set (0.05 sec)