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;

二、配置主从同步

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 = 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 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)