详解Mysql双机热备安装步骤Mysql双机热备安装

概述详解Mysql双机热备安装步骤Mysql双机热备安装 MysqL双机热备安装一、安装mysql

#tar -xf mysql-5.7.18-1.el6.x86_64.rpm-bundle.tar #yum localinstall *.rpm

1.1修改mysql配置

# For advice on how to change settings please see# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html[mysqld]## Remove leading # and set to the amount of RAM for the most important data# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.# innodb_buffer_pool_size = 128M## Remove leading # to turn on a very important data integrity option: logging# changes to the binary log between backups.# log_bin## Remove leading # to set options mainly useful for reporting servers.# The server defaults are faster for transactions and fast SELECTs.# Adjust sizes as needed, experiment to find the optimal values.# join_buffer_size = 128M# sort_buffer_size = 2M# read_rnd_buffer_size = 2Mdatadir=/data/mysqlsocket=/var/lib/mysql/mysql.sock# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pidexplicit_defaults_for_timestamp=truetmpdir=/tmp[client]default-character-set=utf8mb4[mysqld]character_set_server=utf8mb4

1.2权限修改

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

1.3启动mysql服务

[root@172 ~]# service mysqld restartStopping 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;

二、配置主从同步

master1	172.28.8.187
master2	172.28.8.188

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

# For advice on how to change settings please see# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html[mysqld]## Remove leading # and set to the amount of RAM for the most important data# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.# innodb_buffer_pool_size = 128M## Remove leading # to turn on a very important data integrity option: logging# changes to the binary log between backups.# log_bin## Remove leading # to set options mainly useful for reporting servers.# The server defaults are faster for transactions and fast SELECTs.# Adjust sizes as needed, experiment to find the optimal values.# join_buffer_size = 128M# sort_buffer_size = 2M# read_rnd_buffer_size = 2Mdatadir=/data/mysqlsocket=/var/lib/mysql/mysql.sock# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pidexplicit_defaults_for_timestamp=truetmpdir=/tmpcharacter_set_server=utf8mb4server-id=177log-bin=/var/log/mysql/mysql-bin.logread-only=0binlog-ignore-db=mysqlbinlog-ignore-db=information_schemaexpire_logs_days= 365auto-increment-increment = 2auto-increment-offset = 1[client]default-character-set=utf8mb4

2.2 Master2配置my.cnf

#除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\Gmysql> 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\Gmysql> 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 changedmysql>  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 namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql>  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)