MySQL HA Solution 2019(1)Master Slave on MySQL 5.7

标签: mysql ha solution | 发表时间:2019-07-27 22:26 | 作者:sillycat
出处:https://www.iteye.com
MySQL HA Solution 2019(1)Master Slave on MySQL 5.7

Prepare machines ubuntu-master, ubuntu-dev5, ubuntu-dev6

Install MySQL with right version

> sudo apt-get update
> sudo apt-get dist-upgrade

> sudo apt install mysql-server
> sudo apt install mysql-client

In the new version, we need to find the root password from here
> sudo cat /etc/mysql/debian.cnf
# Automatically generated for Debian scripts. DO NOT TOUCH!
[client]
host     = localhost
user     = debian-sys-maint
password = ddHulNSEFzhUFvS1
socket   = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
host     = localhost
user     = debian-sys-maint
password = ddHulNSEFzhUFvS1
socket   = /var/run/mysqld/mysqld.sock

Login in these server with commands
> mysql -u debian-sys-maint -pddHulNSEFzhUFvS1

Set Up Master and Slave
On Master
> sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
Comments out bind-address
# bind-address          = 127.0.0.1

Config the server-id and log file directory
server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log

On Slave ubuntu-dev5
> sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
server-id               = 11
#bind-address           = 127.0.0.1

On Slave ubuntu-dev6
> sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
server-id               = 12
#bind-address           = 127.0.0.1

Restart Master and Slaves
> sudo /etc/init.d/mysql restart

Login on ubuntu-master and set up
> mysql> use mysql;
> grant replication slave on *.* to 'masterslave'@'ubuntu-dev5' identified by '111111';
> grant replication slave on *.* to 'masterslave'@'ubuntu-dev6' identified by '111111';

Show the master status
> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      788 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

The information 788 and mysql-bin.000001 are important and it will be used later.

On Slave ubuntu-dev5
> use mysql;
> change master to master_host='ubuntu-master', master_user='masterslave', master_password='111111', master_log_file='mysql-bin.000001', master_log_pos=788;
> start slave;

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: ubuntu-master
                  Master_User: masterslave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 788
               Relay_Log_File: ubuntu-dev5-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

Do the same on ubuntu-dev6

Verify the Installation
On master
> create database sillycat;
> use sillycat;
> create table branduser(id int primary key, username char(255), age int);
> insert into branduser(id, username, age) values ( 1, "sillycat", 32);

On slave
> use sillycat;
> select * from branduser;
+----+----------+------+
| id | username | age  |
+----+----------+------+
|  1 | sillycat |   32 |
+----+----------+------+
1 row in set (0.00 sec)


References:
https://my.oschina.net/goudingcheng/blog/608490
https://www.cnblogs.com/gl-developer/p/6178608.html
https://blog.csdn.net/kobejayandy/article/details/60869530
https://blog.51cto.com/10316297/2139138
MyCat
https://blog.csdn.net/linuxlsq/article/details/52606225
https://blog.csdn.net/nxw_tsp/article/details/56277430
https://github.com/MyCATApache/Mycat-Server
https://hokkaitao.github.io/new-tool-to-solve-mysql-scalability-problems
Max
http://www.chinastor.com/yw/09263494H016.html
https://www.cnblogs.com/darren-lee/p/7591416.html
proxySQL
https://blog.51cto.com/sumongodb/2130453
https://www.cnblogs.com/zhoujinyi/p/6838685.html
https://github.com/sysown/proxysql
https://proxysql.com/





已有 0 人发表留言,猛击->> 这里<<-参与讨论


ITeye推荐



相关 [mysql ha solution] 推荐:

MySQL HA Solution 2019(3)MyCat

- - 企业架构 - ITeye博客
0  . 0  .       .

MySQL HA Solution 2019(4)MaxScale

- - 企业架构 - ITeye博客
已有 0 人发表留言,猛击->> 这里<<-参与讨论. —软件人才免语言低担保 赴美带薪读研.

MySQL HA Solution 2019(2)ProxySQL

- - 企业架构 - ITeye博客
| hostgroup_id | hostname      | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |.

MySQL HA Solution 2019(1)Master Slave on MySQL 5.7

- - 企业架构 - ITeye博客
已有 0 人发表留言,猛击->> 这里<<-参与讨论. —软件人才免语言低担保 赴美带薪读研.

MySQL HA 的選擇…

- - Gea-Suan Lin's BLOG
Percona 把常見的 MySQL High Availability 選擇整理後發表成 Webinar,投影片在這裡可以看到 (以及下載):「 Choosing a MySQL High Availability Solution」. 沒有太多新的東西,主要還是再次描述 MySQL HA 這塊目前沒有萬靈丹,常見的這幾個方案各有自己的優缺點,會依照環境與需求而產生不同的選擇.

MySQL HA 高可用性,MySQL Cluster 叢集

- - SSORC.tw
而 SQL Node (mysqld程序) 只是讓我們建立資料庫、表的地方 (看得到/var/lib/mysql/XXX),只是 SQL Node 這邊是看不到實際空間用量的. manager node 及所有的 node 都要裝 mysql-cluster (到 mysql 官網下載). manager node 設定,它只要 ndb_mgm 與 ndb_mgmd 而已.

利用keepalived构建高可用MySQL-HA(转)

- - 数据库 - ITeye博客
关于MySQL-HA,目前有多种解决方案,比如heartbeat、drbd、mmm、共享存储,但是它们各有优缺点. heartbeat、drbd配置较为复杂,需要自己写脚本才能实现MySQL自动切换,对于不会脚本语言的人来说,这无疑是一种脑裂问题;对于mmm,生产环境中很少有人用,且mmm 管理端需要单独运行一台服务器上,要是想实现高可用,就得对mmm管理端做HA,这样无疑又增加了硬件开支;对于共享存储,个人觉得MySQL数据还是放在本地较为安全,存储设备毕竟存在单点隐患.

HA-JDBC -

- -
The state manager component is responsible for storing the active status of each database in the cluster, as well as any durability state.

nginx + keepalive 实现HA

- - CSDN博客编程语言推荐文章
主nginx负载均衡器 192.168.166.203. 辅nginx负载均衡器 192.168.166.177. VIP地址 192.168.166.178. 二.修改配置文件为以下内容: [master slave].  state MASTER #(主机为MASTER,备用机为BACKUP).

SUSE Linux HA双机搭建

- - CSDN博客数据库推荐文章
原来的数据库服务器运行在HP DL388G7服务器上面,内存32G,由于业务增长,内存吃紧,加上时不时出现服务器硬件故障,由于是单实例单服务器,存在单点发现,于是打算采取一些措施改善一下:. 2)并搭建服务器操作系统级别的双机. 3)迁移数据库数据到新服务器. 前面已经写过数据迁移相关的文章,题目为“ EXP/IMP迁移数据”,链接如下: http://blog.csdn.net/laven54/article/details/8877940.