3. server服务器安装数据库并实现主主同步_error callingseartservicebyname-CSDN博客

本文链接：https://blog.csdn.net/litao55555/article/details/86584397

1. server1和server2离线安装数据库

参考文章：https://blog.csdn.net/zz657114506/article/details/53553845

1.1 安装新版mysql前，需将系统自带的mariadb-lib卸载

rpm -qa|grep mariadb
打印：mariadb-libs-5.5.56-2.el7.x86_64
rpm -e --nodeps mariadb-libs-5.5.56-2.el7.x86_64

1.2 解压mysql

tar -xvf mysql-5.7.18-1.el7.x86_64.rpm-bundle.tar
ls打印
libaio-0.3.107-10.el6.x86_64.rpm mysql-community-embedded-devel-5.7.18-1.el7.x86_64.rpm
mysql-5.7.18-1.el7.x86_64.rpm-bundle.tar mysql-community-libs-5.7.18-1.el7.x86_64.rpm
mysql-community-client-5.7.18-1.el7.x86_64.rpm mysql-community-libs-compat-5.7.18-1.el7.x86_64.rpm
mysql-community-common-5.7.18-1.el7.x86_64.rpm mysql-community-minimal-debuginfo-5.7.18-1.el7.x86_64.rpm
mysql-community-devel-5.7.18-1.el7.x86_64.rpm mysql-community-server-5.7.18-1.el7.x86_64.rpm
mysql-community-embedded-5.7.18-1.el7.x86_64.rpm mysql-community-server-minimal-5.7.18-1.el7.x86_64.rpm
mysql-community-embedded-compat-5.7.18-1.el7.x86_64.rpm mysql-community-test-5.7.18-1.el7.x86_64.rpm

1.3 使用rpm -ivh命令依次进行安装

rpm -ivh mysql-community-common-5.7.18-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-5.7.18-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-5.7.18-1.el7.x86_64.rpm
安装mysql-community-server-5.7.16-1.el7.x86_64.rpm 前需要安装libaio-0.3.107-10.el6.x86_64.rpm
下载地址：
http://mirror.centos.org/centos/6/os/x86_64/Packages/libaio-0.3.107-10.el6.x86_64.rpm
安装libaio库：
rpm -ivh libaio-0.3.107-10.el6.x86_64.rpm（若在有网情况下可执行yum install libaio）
安装mysql-community-server：
rpm -ivh mysql-community-server-5.7.18-1.el7.x86_64.rpm --nodeps

1.4 初始化数据库

// 指定datadir, 执行后会生成~/.mysql_secret密码文件
mysql_install_db --datadir=/var/lib/mysql
// 初始化，执行生会在/var/log/mysqld.log生成随机密码
mysqld --initialize //若报错，可无视

1.5 更改mysql数据库目录的所属用户及其所属组，并启动mysql数据库

chown mysql:mysql /var/lib/mysql -R
systemctl start mysqld.service

1.6 登录到mysql，更改root用户的密码

// password 通过 cat ~/.mysql_secret 命令可以查看初始密码为EE1t-W+jKXff
mysql -uroot -p’EE1t-W+jKXff’
mysql> set password=password(‘Hust311!’);

1.7 远程登陆授权

mysql> grant all privileges on . to root@’%’ identified by ‘Hust311!’;
mysql> flush privileges;

1.8 设置mysql开机启动

//退出mysql
ctrl+d
// 检查是否已经是开机启动
systemctl list-unit-files | grep mysqld
// 开机启动
systemctl enable mysqld.service

2. server1和server2数据库主主同步

2.1 server1服务器部署

//在my.cnf文件的[mysqld]配置区域添加下面内容：
[root@server1 ~]# vi /etc/my.cnf
server-id = 1
log-bin = mysql-bin
sync_binlog = 1
binlog_checksum = none
binlog_format = mixed
auto-increment-increment = 2
auto-increment-offset = 1
slave-skip-errors = all
#不设置此项，不支持中文
default-character-set=gbk

[root@server1 ~]# systemctl restart mysqld.service
Shutting down MySQL. SUCCESS!
Starting MySQL… SUCCESS!
//进入数据库
mysql -u root -p
//数据同步授权（iptables防火墙开启3306端口）这样I/O线程就可以以这个用户的身份连接到主服务器，并且读取它的二进制日志。
mysql> grant replication slave,replication client on . to root@’%’ identified by “Hust311!”;
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
//最好将库锁住，仅仅允许读，以保证数据一致性；待主主同步环境部署后再解锁；锁住后，就不能往表里写数据，但是重启mysql服务后就会自动解锁！
mysql> flush tables with read lock; //注意该参数设置后，如果自己同步对方数据，同步前一定要记得先解锁！
Query OK, 0 rows affected (0.00 sec)
//查看下log bin日志和pos值位置
mysql> show master status;
±-----------------±---------±-------------±-----------------±------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
±-----------------±---------±-------------±-----------------±------------------+
| mysql-bin.000001 | 599 | | | |
±-----------------±---------±-------------±-----------------±------------------+
1 row in set (0.01 sec)

2.2 server2服务器部署

//在my.cnf文件的[mysqld]配置区域添加下面内容：
[root@server2 ~]# vi /etc/my.cnf
server-id = 2
log-bin = mysql-bin
sync_binlog = 1
binlog_checksum = none
binlog_format = mixed
auto-increment-increment = 2
auto-increment-offset = 2
slave-skip-errors = all
#不设置此项，不支持中文
default-character-set=gbk

[root@server2 ~]# systemctl restart mysqld.service
Shutting down MySQL… SUCCESS!
Starting MySQL… SUCCESS!

//进入数据库
mysql -u root -p
mysql> grant replication slave,replication client on . to root@’%’ identified by “Hust311!”;
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
±-----------------±---------±-------------±-----------------±------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
±-----------------±---------±-------------±-----------------±------------------+
| mysql-bin.000001 | 599 | | | |
±-----------------±---------±-------------±-----------------±------------------+
1 row in set (0.01 sec)

2.3 server1服务器做同步操作

mysql> unlock tables; //先解锁，将对方数据同步到自己的数据库中
mysql> stop slave;
mysql> change master to master_host=‘192.168.111.8’,master_user=‘root’,master_password=‘Hust311!’,master_log_file=‘mysql-bin.000001’,master_log_pos=599;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
查看同步状态，如下出现两个“Yes”，表明同步成功！
//必须在172.16.0.134执行：systemctl stop iptables.service，关闭防火墙
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.0.132
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 430
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 279
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
…
Seconds_Behind_Master: 0
…
这样，server1就和server2实现了主从同步，即server1同步server2的数据。

2.4 master2服务器做同步操作

mysql> unlock tables; //先解锁，将对方数据同步到自己的数据库中
mysql> stop slave;
mysql> change master to master_host=‘192.168.111.7’,master_user=‘root’,master_password=‘Hust311!’,master_log_file=‘mysql-bin.000001’,master_log_pos=599;
Query OK, 0 rows affected, 2 warnings (0.06 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
//必须在172.16.0.133执行：systemctl stop iptables.service，关闭防火墙
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.0.131
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 430
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 279
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
…
Seconds_Behind_Master: 0
…
这样，server2就和server1实现了主从同步，即server2也同步server1的数据。
以上表明双方已经实现了mysql主主同步。

若报错：error connecting to master ‘root@172.16.0.133:3306’
mysql -u root -h 172.16.0.133 -p
报错：ERROR 2003 (HY000): Can’t connect to MySQL server on ‘172.16.0.133’ (113)
service iptables stop //实际上是执行systemctl stop iptables，命令systemctl出错
报错：Error getting authority: Error initializing authority: Error calling StartServiceByName for org.freedesktop.PolicyKit1: Timeout was reached (g-io-error-quark, 24)

2.5 主主同步效果验证

1）在server1数据库上写入新数据
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
mysql> create database test1;
Query OK, 1 row affected (0.01 sec)
mysql> use test1;
Database changed
mysql> create table if not exists sync1 (
-> id int(10) PRIMARY KEY AUTO_INCREMENT,
-> name varchar(50) NOT NULL);
Query OK, 0 rows affected (0.04 sec)
mysql> insert into sync1 values(1,“zhangsan”);
Query OK, 1 row affected (0.00 sec)
mysql> insert into sync1 values(2,“lisi”);
Query OK, 1 row affected (0.00 sec)
mysql> select * from sync1;
±—±---------+
| id | name |
±—±---------+
| 1 | zhangsan |
| 2 | lisi |
±—±---------+
2 rows in set (0.13 sec)