Mysql双机热备安装

来到golang学习网的大家，相信都是编程学习爱好者，希望在这里学习数据库相关编程知识。下面本篇文章就来带大家聊聊《Mysql双机热备安装》，介绍一下MySQL，希望对大家的知识积累有所帮助，助力实战开发！

一、安装mysql

# For advice on how to change settings please see
# https://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 = 2M
datadir=/data/mysql
socket=/var/lib/mysql/mysql.sock

# 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 ~]# service mysqld restart
Stopping mysqld:                                           [FAILED]
Initializing MySQL database:                               [  OK  ]
Installing validate password plugin:                       [  OK  ]
Starting mysqld:                                           [  OK  ]

1.4查看temp密码

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

db2

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

二、配置主从同步

2.1 配置master1给master2登录的密码

Master1

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

2.2.1 Master2给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

如出现以下两项，则说明配置成功！

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

好了，本文到此结束，带大家了解了《Mysql双机热备安装》，希望本文对你有所帮助！关注golang学习网公众号，给大家分享更多数据库知识！