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/