一、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 | mysql> GRANT REPLICATION SLAVE ON *.* TO 'root'@'%' IDENTIFIED BY '123456'; |
切换到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 | mysql> UNLOCK TABLES; |
到这里,主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.更新主数据库一条数据,查看从数据库变化