Mysql双机热备安装

一、安装mysql

Idtar -xf mysql-5.7.18-1.el6.x86_64.rpm-bundle.tar 
Idyum localinstall *.rpm

1.1修改mysql配置

Id For advice on how to change settings please see
Id http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
Id
Id Remove leading Id and set to the amount of RAM for the most important data
Id cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
Id innodb_buffer_pool_size = 128M
Id
Id Remove leading Id to turn on a very important data integrity option: logging
Id changes to the binary log between backups.
Id log_bin
Id
Id Remove leading Id to set options mainly useful for reporting servers.
Id The server defaults are faster for transactions and fast SELECTs.
Id Adjust sizes as needed, experiment to find the optimal values.
Id join_buffer_size = 128M
Id sort_buffer_size = 2M
Id read_rnd_buffer_size = 2M
datadir=/data/mysql
socket=/var/lib/mysql/mysql.sock

Id Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
explicit_defaults_for_timestamp=true
tmpdir=/tmp

[client]
default-character-set=utf8mb4

[mysqld]
character_set_server=utf8mb4

1.2权限修改

[root@172 ~]Id chown -R mysql:mysql /data
[root@172 ~]Id chmod 777 -R /data/
[root@172 ~]Id chmod -R 777 /tmp

1.3启动mysql服务

[root@172 ~]Id service mysqld restart
Stopping mysqld:                                           [FAILED]
Initializing MySQL database:                               [  OK  ]
Installing validate password plugin:                       [  OK  ]
Starting mysqld:                                           [  OK  ]

1.4查看temp密码

more /var/log/mysqld.log |grep temporary

1.5修改root密码

db1

ALTER USER 'root'@'localhost' IDENTIFIED BY '*****';
flush privileges;
exit;

db2

ALTER USER 'root'@'localhost' IDENTIFIED BY '*****';
flush privileges;
exit;

二、配置主从同步

2.1 配置master1给master2登录的密码

Master1

create user 'repl' identified by '*****';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'172.28.8.188' IDENTIFIED BY '*****';
FLUSH PRIVILEGES;
mysql> create database mydb default charset utf8;

在172.28.8.188测试repuser是否能登录172.28.8.187上的数据库

mysql -urepl -p -h172.28.8.187

2.1.1 Master1配置my.cnf

Id For advice on how to change settings please see
Id http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
Id
Id Remove leading Id and set to the amount of RAM for the most important data
Id cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
Id innodb_buffer_pool_size = 128M
Id
Id Remove leading Id to turn on a very important data integrity option: logging
Id changes to the binary log between backups.
Id log_bin
Id
Id Remove leading Id to set options mainly useful for reporting servers.
Id The server defaults are faster for transactions and fast SELECTs.
Id Adjust sizes as needed, experiment to find the optimal values.
Id join_buffer_size = 128M
Id sort_buffer_size = 2M
Id read_rnd_buffer_size = 2M
datadir=/data/mysql
socket=/var/lib/mysql/mysql.sock

Id Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
explicit_defaults_for_timestamp=true
tmpdir=/tmp
character_set_server=utf8mb4

server-id=177
log-bin=/var/log/mysql/mysql-bin.log
read-only=0
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
expire_logs_days= 365
auto-increment-increment = 2
auto-increment-offset = 1

[client]
default-character-set=utf8mb4

2.2 Master2配置my.cnf

Id除server-id外，其他与master1保持一致

2.2.1 Master2给Master1创建账号密码并授权

create user 'repl' identified by '*****';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'172.28.8.187' IDENTIFIED BY '*****';
FLUSH PRIVILEGES;

2.3 查看Master同步状态

master1

mysql> show master status;
+------------------+----------+--------------+--------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB         | Executed_Gtid_Set |
+------------------+----------+--------------+--------------------------+-------------------+
| mysql-bin.000001 |      154 | mydb         | mysql,information_schema |                   |
+------------------+----------+--------------+--------------------------+-------------------+
1 row in set (0.00 sec)

master2

mysql> show master status;
+------------------+----------+--------------+--------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB         | Executed_Gtid_Set |
+------------------+----------+--------------+--------------------------+-------------------+
| mysql-bin.000001 |      154 | mydb         | mysql,information_schema |                   |
+------------------+----------+--------------+--------------------------+-------------------+
1 row in set (0.00 sec)

设置master1从master2同步

mysql>CHANGE MASTER TO MASTER_HOST='172.28.8.188',MASTER_PORT=3306,MASTER_USER='repl',MASTER_PASSWORD='b4l:GGtG3s0*',MASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=860;
mysql> SHOW SLAVE STATUS\G
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G

设置master2从master1同步

mysql>CHANGE MASTER TO MASTER_HOST='172.28.8.187',MASTER_PORT=3306,MASTER_USER='repl',MASTER_PASSWORD='2S1*8pr+BzqH^8T`',MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=1497;
mysql> SHOW SLAVE STATUS\G
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G

           Slave_IO_Running: Yes
           Slave_SQL_Running: Yes

3.双主同步测试

进入master1 mysql 数据库

mysql>  create database crm;
Query OK, 1 row affected (0.00 sec)

mysql>  use crm;
Database changed
mysql>  create table employee(id int auto_increment,name varchar(10),primary key(id));
Query OK, 0 rows affected (0.00 sec)

mysql>  insert into employee(name) values('a');
Query OK, 1 row affected (0.00 sec)

mysql>  insert into employee(name) values('b');
Query OK, 1 row affected (0.00 sec)

mysql>  insert into employee(name) values('c');
Query OK, 1 row affected (0.06 sec)

mysql>  select * from employee;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  3 | b    |
|  5 | c    |
+----+------+
3 rows in set (0.00 sec)

进入master2，查看是否有crm这个数据库和employee表。

mysql>  show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| crm                |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

mysql>  use crm;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>  show tables;
+---------------+
| Tables_in_crm |
+---------------+
| employee      |
+---------------+
1 row in set (0.00 sec)

mysql>  select * from employee;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  3 | b    |
|  5 | c    |
+----+------+
3 rows in set (0.00 sec)

mysql>  insert into employee(name) values('d');
Query OK, 1 row affected (0.00 sec)

mysql>  select * from employee;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  3 | b    |
|  5 | c    |
|  7 | d    |
+----+------+
4 rows in set (0.00 sec)

在master1的中查看是否有刚刚在master2中插入的数据。

mysql>  select * from employee;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  3 | b    |
|  5 | c    |
|  7 | d    |
+----+------+
4 rows in set (0.00 sec)