数据库：mysql 数据库主从搭建

在编写文章前，有几个问题需要思考一下：

• 为什么要实现主从数据库？
• 主从数据库同步原理
• 如何实现主从数据库？

1. 为什么要实现主从数据库？

从所周知，随着用户量的增多，数据库操作往往会成为一个系统的瓶颈所在，而且一般的系统 "读" 的压力远远大于 "写"，因此我们可以通过实现数据库的读写分离提高系统的性能。

通过设置主从数据库实现读写分离，主数据库负责 "写操作"，从数据库负责 "读操作"，根据压力情况，从数据库可以部署多个提高 "读" 的速度，借此来提高系统总体的性能。

2. 主从数据库同步原理

要实现读写分离，就要解决主从数据库数据同步的问题，在主数据库写入数据后要保证从数据库的数据也要更新。

主服务器 master 记录数据库操作日记到 Binary log，从服务器开启 I/O 线程将二进制日记记录的操作同步到 relay log（存在从服务器的缓存中），另外 SQL 线程将 relay log 日记记录的操作在从服务器执行。

3. 如何实现主从数据库？

3.1 在同一电脑上搭建两个 mysql 服务

1、把 "C:\Program Files\MySQL" 目录下的文件拷贝到一个新建的目录 "C:\mysql3307"；

2、拷贝 "C:\ProgramData\MySQL\MySQL Server 5.6\my.ini" 文件到 "C:\mysql3307\mysql_base" 目录，修改端口、数据库路径等信息：

# 应用程序访问数据库端口号
# pipe
# socket=0.0
port=3307

# 数据库进程监听的端口
# The TCP/IP Port the MySQL Server will listen on
port=3307

# 数据库安装路径
# Path to installation directory. All paths are usually resolved relative to this.
basedir="C:/mysql3307/MySQL Server 5.6/"

# 数据库数据存放路径
# Path to the database root
datadir=C:/mysql3307/mysql_base/data

3、进入从服务器的 "bin" 目录，创建启动服务：

mysqld install MySQL2 --defaults-file="C:\mysql3307\mysql_base\my.ini" 

碰到的问题：

1、在创建服务的时候出现：Install/Remove of the Service Denied!

这个错误出现的原因是没有创建服务的权限，应该以 "用管理员身份打开" 运行 cmd.exe。

2、服务创建成功后启动服务报错："错误 1067：进程意外终止。"

• 进入 "事件查看器" -> "应用程序" 果然发现非常多MySql的错误：Can't open and lock privilege tables:Table 'mysql.user' doesn't exist；
• 原因：数据库的 user 表有问题，把 data\mysql 目录下的 user.frm、user.MYD、user.MYI 拷贝到 "C:\mysql3307\mysql_base\data\mysql" 下。

3.2 主数据库配置

1、在主服务器 master 上配置开启 Binary log，主要在 my.int 下添加：

server-id=1 # 设置服务器唯一的id，默认是1
log-bin=master-bin  # 启用二进制日志
log-bin-index=master-bin.index

2、重启数据库服务器；

3、使用 "show master status;" 查看设置：

+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 |      941 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+

3.3 从数据库配置

在从服务器 my.ini 添加一下信息：

server-id=2
relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin

注意：这里的 server-id 一定要和主库的不同。

配置两个数据库的关联

首先我们先建立一个操作主从同步的数据库用户，切换到主数据执行：

mysql> create user repl;
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'从xxx.xxx.xxx.xx' IDENTIFIED BY 'mysql';
mysql> flush privileges;

创建一个 repl 数据库用户，密码 mysql，从服务器使用 repl 这个账号和主服务器连接的时候，就赋予 PEPLICATION SLAVE 权限，*.* 表示这个权限针对主库的所有表，其中 @ 后面的标识从服务器地址。

从服务器配置：

mysql> change master to master_host='主xxx.xxx.xxx.xx',master_port=3306,master_user='repl',master_password='mysql',master_log_file='master-bin.000001',master_log_pos=0;

在从服务器上启动配置：

mysql> start slave;

停止主从同步命令

mysql> stop slave;

查看状态命令，\G 表示换行查看：

mysql> show slave status \G; 

*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: 192.168.16.167
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000001
          Read_Master_Log_Pos: 4
               Relay_Log_File: WIN-01706271635-relay-bin.000003
                Relay_Log_Pos: 4
        Relay_Master_Log_File: master-bin.000001
             Slave_IO_Running: No
            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: 4
              Relay_Log_Space: 120
              Until_Condition: None