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

MySQLHASolution2019(1)MasterSlaveonMySQL5.7

Preparemachinesubuntu-master,ubuntu-dev5,ubuntu-dev6

InstallMySQLwithrightversion

>sudoapt-getupdate

>sudoapt-getdist-upgrade

>sudoaptinstallmysql-server

>sudoaptinstallmysql-client

Inthenewversion,weneedtofindtherootpasswordfromhere

>sudocat/etc/mysql/debian.cnf

#AutomaticallygeneratedforDebianscripts.DONOTTOUCH!

[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

Loginintheseserverwithcommands

>mysql-udebian-sys-maint-pddHulNSEFzhUFvS1

SetUpMasterandSlave

OnMaster

>sudovi/etc/mysql/mysql.conf.d/mysqld.cnf

Commentsoutbind-address

#bind-address=127.0.0.1

Configtheserver-idandlogfiledirectory

server-id=1

log_bin=/var/log/mysql/mysql-bin.log

OnSlaveubuntu-dev5

>sudovi/etc/mysql/mysql.conf.d/mysqld.cnf

server-id=11

#bind-address=127.0.0.1

OnSlaveubuntu-dev6

>sudovi/etc/mysql/mysql.conf.d/mysqld.cnf

server-id=12

#bind-address=127.0.0.1

RestartMasterandSlaves

>sudo/etc/init.d/mysqlrestart

Loginonubuntu-masterandsetup

>mysql>usemysql;

>grantreplicationslaveon*.*to'masterslave'@'ubuntu-dev5'identifiedby'111111';

>grantreplicationslaveon*.*to'masterslave'@'ubuntu-dev6'identifiedby'111111';

Showthemasterstatus

>showmasterstatus;

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

|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set|

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

|mysql-bin.000001|788||||

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

1rowinset(0.00sec)

Theinformation788andmysql-bin.000001areimportantanditwillbeusedlater.

OnSlaveubuntu-dev5

>usemysql;

>changemastertomaster_host='ubuntu-master',master_user='masterslave',master_password='111111',master_log_file='mysql-bin.000001',master_log_pos=788;

>startslave;

mysql>showslavestatus\G;

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

Slave_IO_State:Waitingformastertosendevent

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

Dothesameonubuntu-dev6

VerifytheInstallation

Onmaster

>createdatabasesillycat;

>usesillycat;

>createtablebranduser(idintprimarykey,usernamechar(255),ageint);

>insertintobranduser(id,username,age)values(1,"sillycat",32);

Onslave

>usesillycat;

>select*frombranduser;

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

|id|username|age|

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

|1|sillycat|32|

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

1rowinset(0.00sec)

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/

相关推荐