43 MySQL复制及Galera Cluster、MHA

最新推荐文章于 2024-08-11 16:01:20 发布

weixin_33737134

最新推荐文章于 2024-08-11 16:01:20 发布

阅读量90

点赞数

文章标签：数据库开发工具运维

原文链接：http://blog.51cto.com/sihua/1876321

版权

01 MySQL复制及MHA

实战：配置MHA

配置：

node1: MHA 192.168.1.130 CentOS7.2

node2：Master 192.168.1.131 CentOS7.2

node3：Slave 192.168.1.132 CentOS7.2

node4：Slave 192.168.1.133 CentOS7.2

一、准备mysql复制环境

[root@node2 ~]# yum -y install mariadb-server

[root@node2 ~]# vim /etc/my.cnf

添加

innodb_file_per_table = 1

skip_name_resolve = 1

log-bin = master-bin

relay-log = relay-bin

server_id = 1

[root@node2 ~]# systemctl start mariadb.service

[root@node2 ~]# mysql

MariaDB [(none)]> SHOW MASTER STATUS;

+-------------------+----------+--------------+------------------+

+-------------------+----------+--------------+------------------+

| master-bin.000003 | 245 | | |

+-------------------+----------+--------------+------------------+

MariaDB [(none)]> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repluser'@'192.168.1.%' IDENTIFIED BY 'replpass';

MariaDB [(none)]> FLUSH PRIVILEGES;

[root@node3 ~]# yum -y install mariadb-server

[root@node4 ~]# yum -y install mariadb-server

[root@node3 ~]# vim /etc/my.cnf

添加

innodb_file_per_table = 1

skip_name_resolve = 1

log-bin = master-bin

relay-log = relay-bin

server_id = 2

read_only = 1

relay_log_purge = 0

[root@node3 ~]# scp /etc/my.cnf node4:/etc

[root@node4 ~]# vim /etc/my.cnf

修改

server_id = 2

为

server_id = 3

[root@node3 ~]# systemctl start mariadb.service

[root@node3 ~]# mysql

MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.1.131',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_LOG_FILE='master-bin.000003',MASTER_LOG_POS=245;

MariaDB [(none)]> START SLAVE;

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SHOW SLAVE STATUS \G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.1.131

Master_User: repluser

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: master-bin.000003

Read_Master_Log_Pos: 498

Relay_Log_File: relay-bin.000002

Relay_Log_Pos: 783

Relay_Master_Log_File: master-bin.000003

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

[root@node4 ~]# systemctl start mariadb.service

[root@node4 ~]# mysql

MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.1.131',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_LOG_FILE='master-bin.000003',MASTER_LOG_POS=245;

MariaDB [(none)]> START SLAVE;

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SHOW SLAVE STATUS \G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.1.131

Master_User: repluser

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: master-bin.000003

Read_Master_Log_Pos: 498

Relay_Log_File: relay-bin.000002

Relay_Log_Pos: 783

Relay_Master_Log_File: master-bin.000003

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

#node2（master节点）

MariaDB [(none)]> GRANT ALL ON *.* TO 'mhauser'@'192.168.1.%' IDENTIFIED BY 'mhapass';Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.00 sec)

#在node1节点上配置4台主机能互相通信

[root@node1 ~]# ssh-keygen -t rsa -P ''

[root@node1 ~]# cat .ssh/id_rsa.pub > .ssh/authorized_keys

[root@node1 ~]#scp -p .ssh/id_rsa .ssh/authorized_keys node2:/root/.ssh

[root@node1 ~]#scp -p .ssh/id_rsa .ssh/authorized_keys node3:/root/.ssh

[root@node1 ~]#scp -p .ssh/id_rsa .ssh/authorized_keys node4:/root/.ssh

[root@node1 ~]# ls *rpm

mha4mysql-manager-0.56-0.el6.noarch.rpm

mha4mysql-node-0.56-0.el6.noarch.rpm

[root@node1 ~]# rpm -ivh epel-release-latest-7.noarch.rpm

[root@node1 ~]# yum install mha4mysql-* -y

[root@node1 ~]# scp mha4mysql-node-0.56-0.el6.noarch.rpm node2:/root

[root@node1 ~]# scp mha4mysql-node-0.56-0.el6.noarch.rpm node3:/root

[root@node1 ~]# scp mha4mysql-node-0.56-0.el6.noarch.rpm node4:/root

[root@node2 ~]# yum -y install mha4mysql-node-0.56-0.el6.noarch.rpm

[root@node3 ~]# yum -y install mha4mysql-node-0.56-0.el6.noarch.rpm

[root@node4 ~]# yum -y install mha4mysql-node-0.56-0.el6.noarch.rpm

[root@node1 ~]# mkdir /etc/masterha

[root@node1 ~]# vim /etc/masterha/app1.cnf

添加

[server default]

user=mhauser

password=mhapass

manager_workdir=/data/masterha/app1

manager_log=/data/masterha/app1/manager.log

remote_workdir=/data/masterha/app1

ssh_user=root

repl_user=repluser

repl_password=replpass

ping_interval=1

[server1]

hostname=192.168.1.131

[server2]

hostname=192.168.1.132

candidate_master=1

[server3]

hostname=192.168.1.133

#检查ssh环境

[root@node1 ~]# masterha_check_ssh --conf=/etc/masterha/app1.cnf

#检查主从复制环境

[root@node1 ~]# masterha_check_repl --conf=/etc/masterha/app1.cnf

#启动MHA

[root@node1 ~]# masterha_manager --conf=/etc/masterha/app1.cnf

#测试：关掉master mysql

[root@node2 ~]# killall mysqld mysqld_safe

测试结果：

#master已变为node3（192.168.1.132）

[root@node4 ~]# mysql

MariaDB [(none)]> SHOW SLAVE STATUS \G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.1.132

Master_User: repluser

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: master-bin.000003

Read_Master_Log_Pos: 245

Relay_Log_File: relay-bin.000002

Relay_Log_Pos: 530

Relay_Master_Log_File: master-bin.000003

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'read_only';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| read_only | ON |

+---------------+-------+

#node3自动变为master，read_only状态被关闭

[root@node3 ~]# mysql

MariaDB [(none)]> SHOW MASTER STATUS;

+-------------------+----------+--------------+------------------+

+-------------------+----------+--------------+------------------+

| master-bin.000003 | 245 | | |

+-------------------+----------+--------------+------------------+

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'read_only';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| read_only | OFF |

+---------------+-------+

[root@node2 ~]# vim /etc/my.cnf

添加

read_only = 1

relay_log_purge = 0

[root@node2 ~]# rm -rf /var/lib/mysql/*

[root@node2 ~]# systemctl start mariadb.service

[root@node2 ~]# mysql

MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.1.132',MASTER_USER='repluser',MASTER_PASSWORD='replpass';

MariaDB [(none)]> START SLAVE;

Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> SHOW SLAVE STATUS \G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.1.132

Master_User: repluser

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: master-bin.000003

Read_Master_Log_Pos: 245

Relay_Log_File: relay-bin.000002

Relay_Log_Pos: 25940

Relay_Master_Log_File: master-bin.000001

Slave_IO_Running: Yes

Slave_SQL_Running: No

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 1062

Last_Error: Error 'Duplicate entry '%-test-' for key 'PRIMARY'' on query. Default database: 'mysql'. Query: 'INSERT INTO db SELECT * FROM tmp_db WHERE @had_db_table=0'

MariaDB [(none)]> \q

[root@node2 ~]# systemctl stop mariadb.service

[root@node2 ~]# rm -rf /var/lib/mysql/*

[root@node2 ~]# systemctl start mariadb.service

[root@node2 ~]# mysql

MariaDB [(none)]> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repluser'@'192.168.1.%' IDENTIFIED BY 'replpass';

MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.1.132',MASTER_USER='repluser',MASTER_PASSWORD='replpass';

MariaDB [(none)]> GRANT ALL ON *.* TO 'mhauser'@'192.168.1.%' IDENTIFIED BY 'mhapass';

MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.1.132',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_LOG_FILE='master-bin.000003',MASTER_LOG_POS=245;

MariaDB [(none)]> START SLAVE;

MariaDB [(none)]> SHOW SLAVE STATUS \G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.1.132

Master_User: repluser

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: master-bin.000003

Read_Master_Log_Pos: 245

Relay_Log_File: relay-bin.000002

Relay_Log_Pos: 530

Relay_Master_Log_File: master-bin.000003

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

[root@node1 ~]# masterha_check_status --conf=/etc/masterha/app1.cnf

app1 (pid:37651) is running(0:PING_OK), master:192.168.1.132

02 MySQL复制及Galera Cluster

实战：配置Galera Cluster

配置环境：

node1: MHA 192.168.1.130 CentOS7.2

node2：Master 192.168.1.131 CentOS7.2

node3：Slave 192.168.1.132 CentOS7.2

node4：Slave 192.168.1.133 CentOS7.2

环境准备

[root@node1 ~]# vim /etc/hosts

添加

192.168.1.131 node1

192.168.1.132 node2

192.168.1.133 node3

[root@node1 ~]# scp /etc/hosts node2:/etc

[root@node1 ~]# scp /etc/hosts node3:/etc

#在node1节点上配置3台主机能互相通信

[root@node1 ~]# ssh-keygen -t rsa -P ''

[root@node1 ~]# cat .ssh/id_rsa.pub > .ssh/authorized_keys

[root@node1 ~]#scp -p .ssh/id_rsa .ssh/authorized_keys node2:/root/.ssh

[root@node1 ~]#scp -p .ssh/id_rsa .ssh/authorized_keys node3:/root/.ssh

[root@node1 ~]# cd /etc/yum.repos.d/

[root@node1 yum.repos.d]# vim galera.repo

添加

[galera]

name=Galera Cluster from MariaDB

baseurl=ftp://192.168.1.131/pub/galera_cluster/

gpgcheck=0

[root@node1 ~]# rpm -ivh /mnt/Packages/vsftpd-3.0.2-10.el7.x86_64.rpm

[root@node1 ~]# systemctl start vsftpd.service

[root@node1 ~]# cp -r galer_cluster /var/ftp/pub/

[root@node1 yum.repos.d]# yum repolist

[root@node1 ~]# yum -y install MariaDB-Cluster

[root@node1 ~]# yum list all | grep -i "mariadb"

[root@node1 ~]# yum -y install MariaDB-Galera-server

[root@node1 ~]# scp /etc/yum.repos.d/galera.repo node2:/etc/yum.repos.d/galera.repo

[root@node1 ~]# scp /etc/yum.repos.d/galera.repo node3:/etc/yum.repos.d/galera.repo

[root@node2 ~]# yum -y install MariaDB-Galera-server

[root@node3 ~]# yum -y install MariaDB-Galera-server

[root@node1 ~]# vim /etc/my.cnf.d/server.cnf

修改[galera]的内容为：

[galera]

# Mandatory settings

wsrep_provider=/usr/lib64/galera/libgalera_smm.so

wsrep_cluster_address="gcomm://192.168.1.131,192.168.1.132,192.168.1.133"

binlog_format=row

default_storage_engine=InnoDB

innodb_autoinc_lock_mode=2

bind-address=0.0.0.0

wsrep_cluster_name='mycluster'

[root@node1 ~]# scp /etc/my.cnf.d/server.cnf node2:/etc/my.cnf.d/

[root@node1 ~]# scp /etc/my.cnf.d/server.cnf node3:/etc/my.cnf.d/

#启动

[root@node1 ~]# /etc/rc.d/init.d/mysql start --wsrep-new-cluster

[root@node2 ~]# service mysql start

[root@node3 ~]# service mysql start

[root@node1 ~]# mysql

MariaDB [(none)]> CREATE DATABASE mydb;

MariaDB [(none)]> SHOW DATABASES;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mydb |

| mysql |

| performance_schema |

| test |

+--------------------+

[root@node2 ~]# mysql

MariaDB [(none)]> SHOW DATABASES;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mydb |

| mysql |

| performance_schema |

| test |

+--------------------+

MariaDB [(none)]> use mydb;

Database changed

MariaDB [mydb]> CREATE TABLE tb1(id int,name char(10));

[root@node3 ~]# mysql

MariaDB [(none)]> SHOW DATABASES;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mydb |

| mysql |

| performance_schema |

| test |

+--------------------+

MariaDB [(none)]> use mydb;

MariaDB [mydb]> SHOW TABLES;

+----------------+

| Tables_in_mydb |

+----------------+

| tb1 |

+----------------+

MariaDB [mydb]> DESC tb1;

+-------+----------+------+-----+---------+-------+

+-------+----------+------+-----+---------+-------+

| id | int(11) | YES | | NULL | |

| name | char(10) | YES | | NULL | |

+-------+----------+------+-----+---------+-------+

MariaDB [mydb]> CREATE TABLE tb2(id int UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,name CHAR(30));

MariaDB [mydb]> INSERT INTO tb2 (name) VALUES ('hi'),('hello');

node2节点：

MariaDB [mydb]> SELECT * FROM tb2;

+----+-------+

| id | name |

+----+-------+

| 2 | hi |

| 5 | hello |

+----+-------+

MariaDB [mydb]> INSERT INTO tb2 (name) VALUES ('to'),('from');

MariaDB [mydb]> SELECT * FROM tb2;

+----+-------+

| id | name |

+----+-------+

| 2 | hi |

| 5 | hello |

| 7 | to |

| 10 | from |

+----+-------+

转载于:https://blog.51cto.com/sihua/1876321

weixin_33737134

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
43 MySQL复制及Galera Cluster、MHA

01 MySQL复制及MHA实战：配置MHA 配置： node1: MHA 192.168.1.130 CentOS7.2 node2：Master 192.168.1.131 CentOS7.2 node3：Slave 192.168.1.132 CentOS7.2 node4：Slave 192.168.1.133 CentOS7.2 一、准备mysql复制环境 [...
复制链接

扫一扫