1. 首页
  2. 文章中心
  3. mysql

详解Mysql双机热备安装步骤

118 阅读 0 评论
我是靠谱客的博主 朴实小蘑菇，最近开发中收集的这篇文章主要介绍详解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 = 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 ~]# chown -R mysql:mysql /data
[root@172 ~]# chmod 777 -R /data/
[root@172 ~]# chmod -R 777 /tmp
登录后复制

1.3启动mysql服务

[root@172 ~]# 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;
登录后复制
登录后复制

二、配置主从同步

master1172.28.8.187
master2172.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 = 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
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

#除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 STATUSG
mysql> START SLAVE;
mysql> SHOW SLAVE STATUSG
登录后复制

设置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 STATUSG
mysql> START SLAVE;
mysql> SHOW SLAVE STATUSG
登录后复制
           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)
登录后复制

推荐学习：《mysql视频教程》

以上就是详解Mysql双机热备安装步骤的详细内容，更多请关注靠谱客其它相关文章！

最后

以上就是朴实小蘑菇为你收集整理的详解Mysql双机热备安装步骤的全部内容，希望文章能够帮你解决详解Mysql双机热备安装步骤所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。

本图文内容来源于网友提供，作为学习参考使用，或来自网络收集整理，版权属于原作者所有。
点赞(78)

相关文章

mysql数据库是什么
mysql数据库是什么
一分钟解决mysql_config not found的问题
一分钟解决mysql_config not found的问题
mysql or用法是什么
mysql or用法是什么
mysql和oracle有区别吗
mysql和oracle有区别吗
详解Mysql双机热备安装步骤
详解Mysql双机热备安装步骤
mysql的密码是什么
mysql的密码是什么
mysql怎么查询最后一条记录
mysql怎么查询最后一条记录
一步步教你搭建Mysql单机多实例
一步步教你搭建Mysql单机多实例

评论列表共有 0 条评论

立即
投稿
返回
顶部