mysql读写分离

一、mysql读写分离大致介绍：

使用主从数据库的好处：

• 把主数据库备份到从数据库，它并不影响主数据库的写操作。
• 资源敏感型操作，如数据分析，可以在从数据库上操作，从而不影响主数据库的性能。

我的使用如下安装环境：

• 主从服务器系统都是Ubuntu16.04
• 主服务器IP地址：192.169.150.128
• 从服务器IP地址：192.169.150.129
• 主从服务器都安装使用MySQL
• 主服务器允许远程连接（对外开放3306端口）
• 数据库版本5.7.21  (查看版本命令，进入mysql命令行，输入select version();)

二、准备工作

1.安装mysql

sudo apt-get install mysql-server mysql-client

如果出现以下错

E: Could not get lock /var/lib/dpkg/lock - open (11: Resource temporarily unavailable)

E: Unable to lock the administration directory (/var/lib/dpkg/), is another process using it?

先apt-get update，继续报的话

找到并且杀掉所有的apt-get 和apt进程

ps -A | grep apt

杀掉进程apt-get

kill -9 进程id

继续安装mysql还不行的话

删除锁定文件

锁定的文件会阻止 Linux 系统中某些文件或者数据的访问，这个概念也存在于 Windows 或者其他的操作系统中。

一旦你运行了 apt-get 或者 apt 命令，锁定文件将会创建于 /var/lib/apt/lists/、/var/lib/dpkg/、/var/cache/apt/archives/ 中。

这有助于运行中的 apt-get 或者 apt 进程能够避免被其它需要使用相同文件的用户或者系统进程所打断。当该进程执行完毕后，锁定文件将会删除。

   当你没有看到 apt-get 或者 apt 进程的情况下在上面两个不同的文件夹中看到了锁定文件，这是因为进程由于某个原因被杀掉了，因此你需要删除锁定文件来避免该错误。

首先运行下面的命令来移除 /var/lib/dpkg/ 文件夹下的锁定文件：

$ sudo rm /var/lib/dpkg/lock

之后像下面这样强制重新配置软件包：

$ sudo dpkg --configure -a

也可以删除 /var/lib/apt/lists/ 以及缓存文件夹下的锁定文件：

$ sudo rm /var/lib/apt/lists/lock
$ sudo rm /var/cache/apt/archives/lock

接下来，更新你的软件包源列表：

$ sudo apt update
或者
$ sudo apt-get update

2.配置mysql远程访问

ubuntu安装MySQL的过程中会出现设置初始root密码，暂时设置为123456，

然后vim /etc/mysql/mysql.conf.d/mysqld.cnf

注释#bind-address           = 127.0.0.1

然后进入mysql命令行

mysql -uroot -p123456

执行命令

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;

*.*的意思是第一个*表示数据库，第二个*表示表，*.*是所有库的所有表，如果是某个特定的数据库，则写成dbname.*

刷新权限

flush privileges;

exit

退出

好，现在可以试着使用navicat连一下了

测试OK

三、正式配置

基础环境(ubuntu 16.04,mysql 5.7.21)

主：192.169.150.128

从：192.169.150.129

流程大致说明(配置前需要将所有的关于mysql的操作给关掉，例如工程调用mysql，将工程先关掉)：

主:

1.编辑主mysql配置文件

2.设置从服务器账号和密码

3.锁定数据库,查看相关信息，导出数据库信息（测试时10S之内就导完了，580MB左右）

从：

1.创建需要被同步的数据库

2,导入主数据库导出的.sql文件

3.编辑配置文件

4.重启mysql

5.执行命令，指定主服务器地址

6.激活从服务器

7.查看状态SHOW SLAVE STATUS\G;   如果Slave_IO_Running和Slave_SQL_Running都为yes才表示同步成功。

附： 如果sql文件过大（目前海外的是580MB）  主机间需要配置ssh通信，或者使用连接工具带的上传文件连接器进行上传（此处较慢）

如果配置了ssh通信,

可以使用命令将本机文件发送到远程机器的指定目录下

scp file.log root@192.168.1.110:/root/

或者mysql -h 192.168.1.110 -u -p 连接到远程mysql命令行端，执行source /path/db.sql 运行本机sql文件

aws服务器采用scp方式发送.sql文件:(-i是加载pem文件,wordpress_db.sql是需要发送的文件,ubuntu是从服务器的用户名，后面是从服务器的ip,/root/是从服务器的目录，测试580MB发送用了5s)

scp -i /home/ubuntu/EZ.pem /root/ezhome.sql ubuntu@192.168.150.129:/root/

注意以下配置的数据库全是例子，具体改成需要备份的数据库

主服务器配置（192.169.150.128）：

1.编辑主MySQL的配置文件：

$ sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

或者sudo vim /etc/mysql/my.cnf

找到如下一段：

1	bind-address            = 127.0.0.1

把IP替换为服务器IP：

1	bind-address            = 192.168.150.128

下面更改server-id，它位于[mysqld]段。你可以把它的值改为任何正整数（我把它设为1），但是这个数字在MySQL主从服务器群中必须唯一，这是区别MySQL的一个标识。

取消下面一行注释并把值设置为1：

server-id               = 1

找到log_bin一行，取消注释，它是主从复制log：

log_bin                 = /var/log/mysql/mysql-bin.log

最后，要设置需要复制的数据库，如果你需要复制多个数据库，重复下面这行：

binlog_do_db            = wordpress_db

改为之后，保存退出。

重启MySQL数据库：

1	$ sudo service mysql restart

进入MySQL命令行：

1	$ mysql -u root -p

2.我们需要给从MySQL服务器添加权限。使用下面命令设置slave用户名和密码：

注意root和123456是从数据库的用户名和密码,例子中使用的是root,最好新建数据库用户名，并赋予权限

1 2 3

mysql> GRANT REPLICATION SLAVE ON *.* TO 'root'@'%' IDENTIFIED BY '123456';   mysql> FLUSH PRIVILEGES;

切换到wordpress_db

mysqk> user wordpress_db;

3.为了防止改变，锁定数据库：

1	mysql> FLUSH TABLES WITH READ LOCK;

执行：

1	mysql> SHOW MASTER STATUS;

你会看到类似如下表：

记住Position对应的数字（589）和File（mysql-bin.000001），在配置从MySQL服务器时需要用到。

下一步使用mysqldump导出wordpress_db数据库。如果在同一个终端中使用mysqldump命令，数据库会自动解除锁定，因为这个原因，我们需要再打开一个新的终端窗口，并执行：

1	$ mysqldump -u root -p --opt wordpress_db > /root/wordpress_db.sql

然后发送.sql文件到从服务器,使用如下命令(-i是加载pem文件,wordpress_db.sql是需要发送的文件,ubuntu是从服务器的用户名，后面是从服务器的ip,/root/是从服务器的目录)

scp -i /home/ubuntu/EZ.pem /root/wordpress_db.sql ubuntu@192.168.150.129:/root/

回到前一个终端，解锁数据库，然后退出MySQL命令行：

1 2 3

mysql> UNLOCK TABLES;   mysql> QUIT;

到这里，主MySQL配置完成。

配置从MySQL（192.168.150.129）

小憩一下，准备配置从服务器。

登录从服务器，登录MySQL：

1	$ mysql -u root -p

1.创建需要复制的数据库：

mysql> CREATE DATABASE wordpress_db;

退出：

mysql> EXIT;

2.导入从主MySQL导出的数据库：

$ mysql -u root -p wordpress_db < /root/wordpress_db.sql

3.编辑MySQL配置文件：

$ sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

或者vim /etc/mysql/my.cnf

设置server-id，只要不和主MySQL服务器一样就行（我设置为2）。取消注释，并把值改为2：

server-id               = 2

更新如下行：

log_bin                 = /var/log/mysql/mysql-bin.log

binlog_do_db            = wordpress_db

relay-log               = /var/log/mysql/mysql-relay-bin.log

relay-log是添加的，默认并没有这一行。更改完，保存退出。

4.重启MySQL服务：

1	$ sudo service mysql restart

5.指定主服务器ip:再次进入MySQL命令行，执行：

CHANGE MASTER TO MASTER_HOST='192.168.150.128',MASTER_USER='root', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=  589;

注意：根据你的信息替换。

6.激活从MySQL服务器：

1	mysql> START SLAVE;

7.使用如下命令查看详细状态：

1	mysql> SHOW SLAVE STATUS\G

备注：Slave_IO_Running和Slave_SQL_Running都为yes才表示同步成功。

到此，一个基本的MySQL主从复制配置完成。

测试：

1.向主数据库插入一条数据，查看从数据库是否有内容

2.向主数据库删除一条数据，查看从数据库变化

3.更新主数据库一条数据，查看从数据库变化