MySQL主从复制和读写分离

it2024-07-04 45

MySQL主从复制和读写分离

主从复制（一主双备，这边mysql已经都是装好的）1、设置时间同步主服务器主机名设置为1两台从服务器分别设置主机名为2，3 2、配置主从复制主服务器第一台从服务器第二台从服务器测试主从复制读写分离配置jdk配置阿米巴修改阿米巴配置文件测试

主从复制（一主双备，这边mysql已经都是装好的）

1、设置时间同步

主服务器主机名设置为1

[root@localhost ~]# yum -y install ntp [root@localhost ~]# hostnamectl set-hostname 1 [root@localhost ~]# su [root@1 ~]# vim /etc/ntp.conf ##在文本最后加上下面两句代码 server 127.127.1.0 fudge 127.127.1.0 stratum 8 [root@1 ~]# service ntpd restart Redirecting to /bin/systemctl restart ntpd.service [root@1 ~]# systemctl restart ntpd [root@1 ~]# systemctl enable ntpd

两台从服务器分别设置主机名为2，3

[root@localhost ~]# hostnamectl set-hostname 2 [root@localhost ~]# su [root@2 ~]# [root@2 ~]# yum -y install ntpdate Loaded plugins: fastestmirror Determining fastest mirrors centos | 3.6 kB 00:00:00 Package ntpdate-4.2.6p5-28.el7.centos.x86_64 already installed and latest version Nothing to do [root@2 ~]# ##这边是我已经有这个时间同步服务了 [root@2 ~]# ntpdate 20.0.0.60 21 Oct 02:17:22 ntpdate[37995]: adjust time server 20.0.0.60 offset -0.008243 sec [root@2 ~]# crontab -e ##创建周期性计划任务 no crontab for root - using an empty one */2 * * * * /usr/sbin/ntpdate 20.0.0.60 >>/var/log/ntpdate.log crontab: installing new crontab ##刷新计划任务并设为开机自启 [root@2 ~]# systemctl restart crond [root@2 ~]# systemctl enable crond

第二台从服务器跟第一台从服务操作一样

[root@localhost ~]# hostnamectl set-hostname 3 [root@localhost ~]# su [root@3 ~]# yum -y install ntpdate Loaded plugins: fastestmirror Determining fastest mirrors centos | 3.6 kB 00:00:00 Package ntpdate-4.2.6p5-28.el7.centos.x86_64 already installed and latest version Nothing to do [root@3 ~]# ntpdate 20.0.0.60 21 Oct 02:17:39 ntpdate[48587]: adjust time server 20.0.0.60 offset 0.006296 sec [root@3 ~]# crontab -e no crontab for root - using an empty one */2 * * * * /usr/sbin/ntpdate 20.0.0.60 >>/var/log/ntpdate.log crontab: installing new crontab [root@3 ~]# systemctl restart crond [root@3 ~]# systemctl enable crond

2、配置主从复制

主服务器

[root@1 ~]# vim /etc/my.cnf ##修改配置文件[mysqld]模块下面的server-id然后加入下面两行 ##启用二进制文件和允许从服务器同步 server-id = 11 log_bin = master-bin log-slave-updates = ture [root@1 ~]# systemctl restart mysqld ##重启数据库并进入数据库授权 [root@1 ~]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. mysql> grant replication slave on *.* to 'myslave'@'20.0.0.%' identified by 'As123'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> show master status; ###看主服务器的状态，待会从服务器要用到 +-------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-------------------+----------+--------------+------------------+-------------------+ | master-bin.000001 | 599 | | | | +-------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.01 sec)

第一台从服务器

[root@2 ~]# vim /etc/my.cnf ##修改[mysqld]模块下的server id 并添加两行代码，开启中继日志和日志索引 server-id = 22 relay-log = relay-log-bin relay-log-index = slave-relay-bin.index ##重启mysql服务并进入数据库授权 [root@2 ~]# systemctl restart mysqld [root@2 ~]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. mysql> change master to master_host='20.0.0.60',master_user='myslave',master_password='As123',master_log_file='master-bin.0000001',master_log_pos=599; Query OK, 0 rows affected, 2 warnings (0.01 sec) mysql> flush privileges; ##刷新数据库，有时候会不立即生效 Query OK, 0 rows affected (0.00 sec) mysql> start slave; ##开启从服务器 Query OK, 0 rows affected (0.00 sec) mysql> show slave status\G ##查看从服务器状态，\G是竖着看，横着看有点烦。只需要看到IO和SQL两个都是yes状态就说明OK了 *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 20.0.0.60 Master_User: myslave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-bin.000001 Read_Master_Log_Pos: 599 Relay_Log_File: relay-log-bin.000002 Relay_Log_Pos: 321 Relay_Master_Log_File: master-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes

第二台从服务器

和第一台从服务器基本一样的

[root@localhost ~]# vi /etc/my.cnf server-id = 33 relay-log = relay-log-bin relay-log-index = slave-relay-bin.index [root@localhost ~]# systemctl restart mysqld [root@localhost ~]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. mysql> change master to master_host='20.0.0.60',master_user='myslave',master_password='As123',master_log_file='master-bin.0000001 Query OK, 0 rows affected, 2 warnings (0.01 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> start slave; Query OK, 0 rows affected (0.00 sec) mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 20.0.0.60 Master_User: myslave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-bin.000001 Read_Master_Log_Pos: 599 Relay_Log_File: relay-log-bin.000002 Relay_Log_Pos: 321 Relay_Master_Log_File: master-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes

测试主从复制

读写分离

准备工作jdk和amoeba压缩包

[root@amoeba opt]# ll total 83024 -rw-r--r-- 1 root root 3161433 Oct 20 19:59 amoeba-mysql-binary-2.2.0.tar.gz -rwxr-xr-x 1 root root 81849212 Oct 20 20:00 jdk-6u14-linux-x64.bin ##这边我的jdk权限已经给了直接./执行了

配置jdk

[root@amoeba ~]# mv /opt/jdk1.6.0_14 /usr/local/jdk1.6 [root@amoeba ~]# vim /etc/profile ## 设置Java环境变量 export JAVA_HOME=/usr/local/jdk1.6 export CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/lib export PATH=$JAVA_HOME/lib:$JAVA_HOME/jre/bin/:$PATH:$HOME/bin export AMOEBA_HOME=/usr/local/amoeba export PATH=$PATH:$AMOEBA_HOME/bin ##1、java的工作目录 ##2、jre里面放了lib（类库）里面都是类文件 ##3、指定的环境变量，便于识别 ##4、amoeba工作目录 ##5、指定环境变量 [root@amoeba ~]# source /etc/profile ## 刷新文件 [root@amoeba ~]# echo $PATH /usr/local/jdk1.6/lib:/usr/local/jdk1.6/jre/bin/:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin:/root/bin:/usr/local/amoeba/bin ##Jdk和amoeba都在里面，说明环境变量生效了 [root@amoeba opt]# ll drwxr-xr-x 10 root root 321 Oct 21 17:20 jdk1.6.0_14 -rw-r--r-- 1 root root 3161433 Oct 20 19:59 amoeba-mysql-binary-2.2.0.tar.gz -rwxr-xr-x 1 root root 81849212 Oct 20 20:00 jdk-6u14-linux-x64.bin

配置阿米巴

[root@amoeba ~]# mkdir /usr/local/amoeba [root@amoeba ~]# tar zxvf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/ [root@amoeba ~]# /usr/local/amoeba/bin/amoeba amoeba start|stop ##说明已经安装成功了 ###去三台数据库给amoeba授权 mysql> grant all on *.* to test@'20.0.0.%' identified by 'As123'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> flush privileges; ## 刷新一下 Query OK, 0 rows affected (0.00 sec)

修改阿米巴配置文件

[root@amoeba ~]# cd /usr/local/amoeba/conf/ [root@amoeba conf]# vim amoeba.xml ## amoeba申明名称的配置文件

[root@amoeba conf]# vi dbServers.xml

[root@amoeba conf]# /usr/local/amoeba/bin/amoeba start & #开启amoeba服务并放到后台执行 [root@amoeba conf]# yum -y install mariadb* # 安装mysql客户端，用于做测试用 [root@amoeba conf]# systemctl start mariadb.service ## 开启数据库

这边有时候会有提示按下面的顺序做就行了回车（提示输入密码，我们没有密码直接回车） yes 然后输入两次密码（输入新密码） n （删除里面匿名用户） n （禁止用户远程登录） n （删除测试数据库） y （重载里面的表）

[root@amoeba conf]# netstat -anpt |grep 8066 tcp6 0 0 :::8066 :::* LISTEN 33973/java ##端口已经开启，说明服务已经开了 [root@amoeba ~]# mysql -u amoeba -p -h 127.0.0.1 -P8066 ##因为是amoeba本机当测试机用的所以-h指向的是127.0.0.1

测试

MySQL [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | szy | +--------------------+ 5 rows in set (0.01 sec) MySQL [(none)]> create database ig; Query OK, 1 row affected (0.00 sec) MySQL [(none)]> use ig; Database changed MySQL [ig]> create table nb (id int(4)); Query OK, 0 rows affected (0.01 sec)

然后去两台从服务器关闭主从复制

mysql> stop slave; Query OK, 0 rows affected (0.01 sec) mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Master_Host: 20.0.0.60 Master_User: myslave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-bin.000003 Read_Master_Log_Pos: 1212 Relay_Log_File: relay-log-bin.000017 Relay_Log_Pos: 1379 Relay_Master_Log_File: master-bin.000003 Slave_IO_Running: No ##这两个关闭就行了 Slave_SQL_Running: No ##这两个关闭就行了 mysql> use ig Database changed mysql> show tables; +--------------+ | Tables_in_ig | +--------------+ | nb | +--------------+ 1 row in set (0.00 sec) mysql> insert into nb values (1); Query OK, 1 row affected (0.00 sec) mysql> select * from nb; +------+ | id | +------+ | 1 | +------+ 1 row in set (0.00 sec) mysql> stop slave; Query OK, 0 rows affected (0.01 sec) mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Master_Host: 20.0.0.60 Master_User: myslave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-bin.000003 Read_Master_Log_Pos: 1212 Relay_Log_File: relay-log-bin.000017 Relay_Log_Pos: 1379 Relay_Master_Log_File: master-bin.000003 Slave_IO_Running: No ##这两个关闭就行了 Slave_SQL_Running: No ##这两个关闭就行了 mysql> use ig Database changed mysql> show tables; +--------------+ | Tables_in_ig | +--------------+ | nb | +--------------+ 1 row in set (0.00 sec) mysql> insert into nb values (2); Query OK, 1 row affected (0.00 sec) mysql> select * from nb; +------+ | id | +------+ | 2 | +------+ 1 row in set (0.00 sec)

最后去客户机测试

MySQL [ig]> select * from nb; +------+ | id | +------+ | 2 | +------+ 1 row in set (0.00 sec) MySQL [ig]> select * from nb; +------+ | id | +------+ | 1 | +------+ 1 row in set (0.01 sec) MySQL [ig]> select * from nb; +------+ | id | +------+ | 2 | +------+ 1 row in set (0.01 sec) MySQL [ig]> select * from nb; +------+ | id | +------+ | 1 | +------+ 1 row in set (0.01 sec)

结论：发现每一次查询都会轮询在slave1和slave2上查询数据，如果开启主从复制，则数据都会相同读写分离试验成功

最新回复(0)