- 操作系统环境
操作系统环境
rhel6.5
操作系统自带的软件包
两台主机:server7.com
以及server8.com
- 首先安装
mysql-server.x86_64 0:5.1.71-1.el6
这里是系统自带的版本
[root@server7 ~]# yum install mysql-server -y
[root@server7 ~]# /etc/init.d/mysqld start
- 并且会自动执行
Mysql
的初始化操作; - 修改
server7.com
上面的配置文件为
在[mysqld]里面添加
server-id=1
log-bin=mysql-bin
binlog-do-db=test
binlog-ignore-db=mysql
- 然后启动主节点上面的服务,这样
mysqld
的二进制日志文件就会更新
[root@server7 ~]# /etc/init.d/mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
- 查看更新的二进制日志文件
[root@server7 ~]# ll /var/lib/mysql/
total 20496
-rw-rw----. 1 mysql mysql 106 3月 27 08:48 mysql-bin.000001
-rw-rw----. 1 mysql mysql 19 3月 27 08:48 mysql-bin.index
....
- 如果再次重新启动
mysqld
那么服务的配置文件就会再次更新
-rw-rw----. 1 mysql mysql 125 3月 27 08:50 mysql-bin.000001
-rw-rw----. 1 mysql mysql 106 3月 27 08:51 mysql-bin.000002
-rw-rw----. 1 mysql mysql 38 3月 27 08:51 mysql-bin.index
....
- 在进行数据库的主从复制,依赖的就是这些二进制日志文件
- 地写的这些信息表示的是
mysqld
正在使用的mysql
二进制日志的文件以及开始的位置;
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 106 | test | mysql |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
- 需要对远程用户进行授权
mysql> grant replication slave on *.* to linux@'172.25.23.8' identified by 'westos';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec
- 接下来在另一个节点上面安装
mysqld
,尝试使用联系远程主机
[root@server8 ~]# yum install mysql-server -y
[root@server8 ~]# mysql -h 172.25.23.7 -ulinux -p
- 如果出现这个错误
[root@server8 ~]# mysql -h 172.25.23.7 -ulinux -p
Enter password:
ERROR 2003 (HY000): Can't connect to MySQL server on '172.25.23.7' (113)
- 通常表示的是网络问题,例如防火墙端口或者网络是否可达
- 更改
server8.com
的配置文件
[root@server8 ~]# vim /etc/my.cnf
添加:
server-id=2
- 然后重新启动服务
[root@server8 ~]# /etc/init.d/mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
- 在从节点上面,说明开始进行二进制日志同步的用户,密码,二进制日志的文件的名称以及位置,这个命令必须在
mysql
的管理员账户完成,否则体是权限不足
mysql> show slave status;
ERROR 1227 (42000): Access denied; you need the SUPER,REPLICATION CLIENT privilege for this operation
- 使用
mysql
的管理员密码登陆,默认密码为空,进行登陆,然后执行下面的操作
mysql> change master to master_host='172.25.23.7',master_user='linux',master_password='westos',master_log_file='mysql-bin.000002', master_log_pos=332;
Query OK, 0 rows affected (0.87 sec)
- 然后启动从服务器
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
- 通过命令查看
slave
的状态
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.25.23.7
Master_User: linux
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 332
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.000002
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: 332
Relay_Log_Space: 407
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:
1 row in set (0.00 sec)
ERROR:
No query specified
- 需要保证上面执行的过程中没有错误,并且
Slave_IO_Running: Yes
,Slave_SQL_Running: Yes
,Slave_IO_State: Waiting for master to send event
都是正常的,这样在进行master
的数据写入或者修改的过程中,数据能够及时的进行同步
Master_Log_File: mysql-bin.000002 //主节点使用的二进制日志
Relay_Log_File: mysqld-relay-bin.000001 //表示中继日志
- 接下来,对于使用二进制日志进行同步的方式,首先需要确定,
slave
上面存在对应的数据库,所以我们先创建数据库test
这一点是需要注意的,进行数据库数据同步的前提是master
和slave
上面的数据库的库必须都是存在的 - 在
slave
上面执行操作
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
- 在
master
上面执行操作
mysql> use test;
Database changed
mysql> create table test ( username varchar(25), password varchar(30) );
Query OK, 0 rows affected (0.09 sec)
master
上面的表结构
mysql> desc test;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(25) | YES | | NULL | |
| password | varchar(30) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> exit
Bye
[root@server7 ~]#
slave
上面的表结构
mysql> desc test;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(25) | YES | | NULL | |
| password | varchar(30) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> exit;
Bye
[root@server8 ~]#
- 接下来
master
上面插入数据
mysql> insert into test values('user1','123');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test values('user2','234');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test values('user3','345');
Query OK, 1 row affected (0.00 sec)
mysql> select * from test;
+----------+----------+
| username | password |
+----------+----------+
| user1 | 123 |
| user2 | 234 |
| user3 | 345 |
+----------+----------+
3 rows in set (0.00 sec)
mysql> exit;
Bye
- 然后直接在
slave
上面查看这些数据:
mysql> select * from test;
+----------+----------+
| username | password |
+----------+----------+
| user1 | 123 |
| user2 | 234 |
| user3 | 345 |
+----------+----------+
3 rows in set (0.00 sec)
mysql> exit;
Bye
[root@server8 ~]#
- 上面是两个结点之间的主从复制模型,接下来再次增加一个节点
server9.com
[root@server9 ~]# yum install mysql-server -y
- 当存在三台主机时,就存在两种设计方案
a--->b-->c
或者a--->b a--->c
- 接下来修改配置文件
[root@server9 ~]# vim /etc/my.cnf
添加
server-id=3
- 然后重新启动服务器
[root@server9 ~]# /etc/init.d/mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
- 然后确保
server9.com
上面是存在test
库的,但是这个库里面是没有任何的表的信息的; - 因为这个库里面的信息是空的,可以使用二进制日志来进行增量备份,或者使用
mysldump
来进行完整备份,这里使用二进制日志的方式来进行数据库在这里的恢复; - 查看二进制日志,找出正在时用的日志,并且找出插入
table
的位置
- 在这之后还存在插入数据值的操作,找准合适的开始位置,和结束位置
[root@server9 ~]# mysqlbinlog --start-position=537 --stop-position=866 mysql-bin.000002 | mysql <==这里指定的结束位置,表示在底下的内容之前就结束
- 连接数据库查看数据是否已经同步
mysql> select * from test;
+----------+----------+
| username | password |
+----------+----------+
| user1 | 123 |
| user2 | 234 |
+----------+----------+
2 rows in set (0.00 sec)
mysql> quit
- 所以在查看上面的数据时,其实是少了一行的,关于位置的指定这是需要注意的地方,来弥补上面的错误,将
866
开始的日志重新执行一次
[root@server9 ~]# mysqlbinlog --start-position=866 mysql-bin.000002 | mysql
- 然后在进行查看
Database changed
mysql> select * from test;
+----------+----------+
| username | password |
+----------+----------+
| user1 | 123 |
| user2 | 234 |
| user3 | 345 |
+----------+----------+
3 rows in set (0.00 sec)
- 接下来需要在
server8.com
上面授权server9.com
来获得日志
mysql> grant replication slave on *.* to linux@'172.25.23.9' identified by 'westos';
Query OK, 0 rows affected (0.00 sec)
- 然后需要在
server9.com
上面测试连接正常
[root@server9 ~]# mysql -h 172.25.23.8 -ulinux -pwestos
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 19
Server version: 5.1.71 Source distribution
- 然后修改
sever8.com
上面打开中继功能
[root@server8 ~]# vim /etc/my.cnf
添加
server-id=2
log-bin=mysql-bin
binlog-do-db=test
binlog-ignore-db=mysql
log-slave-updates
- 并且需要重新加载服务
[root@server8 ~]# /etc/init.d/mysqld reload
- 更改
master
,并且启动slave
mysql> change master to master_host='172.25.23.7',master_user='linux',master_password='westos',master_log_file='mysql-bin.000001', master_log_pos=106;
Query OK, 0 rows affected (0.37 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
- 查看
slave
的状态
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.25.23.8
Master_User: linux
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 106
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
- 接下来在
master
上面执行数据的插入操作,然后观察后面的数据是否及时的进行了同步
mysql> insert into test value('user00','1111');
Query OK, 1 row affected (0.00 sec)
mysql> select * from test;
+----------+----------+
| username | password |
+----------+----------+
| user1 | 123 |
| user2 | 234 |
| user3 | 345 |
| user00 | 1111 |
+----------+----------+
4 rows in set (0.00 sec)
mysql> exit;
Bye
[root@server7 ~]#
- 在
server8.com
上面的数据
mysql> select * from test;
+----------+----------+
| username | password |
+----------+----------+
| user1 | 123 |
| user2 | 234 |
| user3 | 345 |
| user00 | 1111 |
+----------+----------+
4 rows in set (0.00 sec)
mysql> exit;
Bye
[root@server8 mysql]#
- 最后在
server9.com
上面的数据
mysql> select * from test;
+----------+----------+
| username | password |
+----------+----------+
| user1 | 123 |
| user2 | 234 |
| user3 | 345 |
| user00 | 1111 |
+----------+----------+
4 rows in set (0.00 sec)
mysql> exit;
Bye
[root@server9 ~]#
- 接下来需要一些软件包
- 首先删除已经安装的软件包
[root@server7 mysql]# rpm -e --nodeps mysql.x86_64 mysql-libs.x86_64 mysql-server.x86_64
- 然后安装新的软件包
[root@server7 mysql]# yum install -y mysql-community-libs-5.7.11-1.el6.x86_64.rpm mysql-community-libs-compat-5.7.11-1.el6.x86_64.rpm mysql-community-server-5.7.11-1.el6.x86_64.rpm mysql-community-client-5.7.11-1.el6.x86_64.rpm mysql-community-common-5.7.11-1.el6.x86_64.rpm
[root@server7 mysql]# /etc/init.d/mysqld start
- 接下来需要查找临时安装的密码
2018-03-27T07:18:35.692015Z 1 [Note] A temporary password is generated for root@localhost: -WA&wc_L%3&m
- 使用临时密码登陆,可能需要对于特殊字符进行转义
- 对于这个密码进行转义然后进行登陆
-WA&wc_L%3&m
[root@server7 mysql]# mysql -p-WA\&wc_L%3\&m
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.11
- 登陆之后,必须首先修改密码然后才能够进行操作
mysql> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
- 执行
mysql
安全安装脚本执行安装
[root@server7 mysql]# mysql_secure_installation
Securing the MySQL server deployment.
Enter password for user root: <==需要输入之前的密码,才能够进行修改;,密码必须符合安全性要求
- 然后需要编辑配置文件,添加主从节点的配置信息
[root@server7 mysql]# vim /etc/my.cnf
server-id=1
log-bin=mysql-bin
binlog-do-db=test
- 之后重启
mysqld
服务
[root@server7 mysql]# /etc/init.d/mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
- 接下来还需要一个主机
server9.com
安装和server8.com
以上同样的服务 - 首先安装需要的软件包
[root@server9 mysql]# yum install -y mysql-community-libs-5.7.11-1.el6.x86_64.rpm mysql-community-libs-compat-5.7.11-1.el6.x86_64.rpm mysql-community-server-5.7.11-1.el6.x86_64.rpm mysql-community-client-5.7.11-1.el6.x86_64.rpm mysql-community-common-5.7.11-1.el6.x86_64.rpm
- 然后删除上一次的
mysql
运行目录
[root@server9 mysql]# rm -fr /var/lib/mysql/
- 启动新安装的
mysql
服务
[root@server9 mysql]# /etc/init.d/mysqld start
Initializing MySQL database: [ OK ]
Installing validate password plugin: [ OK ]
Starting mysqld: [ OK ]
- 然后需要找到临时的密码,
[root@server9 mysql]# cat /var/log/mysqld.log | grep password | less
2018-03-27T07:52:31.306667Z 1 [Note] A temporary password is generated for root@localhost: oe:f66C.%yz8 <<===这个是密码
2018-03-27T07:55:05.853151Z 0 [Note] Execution of init_file '/var/lib/mysql/install-validate-password-plugin.48TVj1.sql' started.
2018-03-27T07:55:06.649276Z 0 [Note] Execution of init_file '/var/lib/mysql/install-validate-password-plugin.48TVj1.sql' ended.
2018-03-27T07:55:09.713243Z 0 [Note] Shutting down plugin 'sha256_password'
2018-03-27T07:55:09.713253Z 0 [Note] Shutting down plugin 'mysql_native_password'
2018-03-27T07:55:18.039981Z 2 [Note] Access denied for user 'UNKNOWN_MYSQL_USER'@'localhost' (using password: NO)
2018-03-27T07:57:30.267076Z 3 [Note] Access denied for user 'root'@'localhost' (using password: NO)
- 然后同样的需要修改密码
[root@server9 mysql]# mysql_secure_installation
Securing the MySQL server deployment.
Enter password for user root:
Error: Access denied for user 'root'@'localhost' (using password: YES)
[root@server9 mysql]# mysql_secure_installation
Securing the MySQL server deployment.
Enter password for user root: