Centos6.5 Based on MariaDB10.x master-slave replication High Availability

Centos6.5 Based on MariaDB10.x master-slave replication High Availability

Master-slave replication objective:

The stability of the mysql server is improved to avoid affecting the entire business after a single mysql server goes down. When the server goes down, the slave server can be immediately upgraded to the new master server. In this way, SQL High Availability redundancy is achieved.

I. Demo Environment

OS: centos6.5

SQL: mariadb-10.0.12

Iptables off

Selinux disabled

Installed Components:

Development tools

Server Platform Development

Host master: 10.19.90.197

Slave: 10.19.90.111

II. (Note: SQL installation is not demonstrated. Please refer to explain)

Master Server Configuration:

1. Stop the service

#/etc/init.d/mysqldstop

2. Modify the mysql configuration file/etc/my. cnf. Here I only modify two parameters, one is the mysql Log Path and the other is the server-id 1.

3. Create a binlog directory and modify the group

#mkdir-pv/mydata/binlogs/ #chown-Rmysql:mysql/mydata/binlogs/

4. Start the mysql service and view the startup information.

#/etc/init.d/mysqldstart

5. log on to mysql to authorize a remote user

MariaDB[(none)]> grant replicationslave,replicationclient on *.* to 'Users' @ '10.19.90.111' identified by 'Password' ; // Note: the user password must be the user and password created in the current SQL statement. MariaDB[(none)]>flush privileges ; // Refresh the MySQL system permission table

Slave Server Configuration:

1. Stop the service

#/etc/init.d/mysqldstop

2. Modify the mysql configuration file/etc/my. cnf. Here I only modify two parameters: one is to enable the mysql relay Log Path and the other is server-id 2.

3. Create a binlog directory and modify the group

#mkdir-pv/mydata/binlogs/ #chown-Rmysql:mysql/mydata/binlogs/

4. Start the mysql service and view the startup information.

#/etc/init.d/mysqldstart

5. Connect to mysql and view the relay log status:

(Note: Relay log: it is actually related to replication. It is almost the same as binary log, except that it is not used to record events, but as the source for reading data and executing it locally, of course, the relay log is on the slave server)

6. view the verification (no file at this time)

#ls/mydata/relaylogs

7. log on to mysql to authorize a remote user

MariaDB[(none)]>grantreplicationslave,replicationclienton*.*to'Users'@'10.19.90.197'identifiedby'Password'; // Note: the user password must be the user and password created in the current SQL statement.MariaDB[(none)]>flushprivileges; // Refresh the MySQL system permission table

3. view the status of master and slave nodes and enable slave nodes

2. view the log status by using maser:

3. Check the synchronization status of the slave node.(Note: The matching diagram is in the synchronization status. After the creation is completed, Slave_IO_Running: No, Slave_ SQL _Running: No. The two parameters are mainly used to view the parameters)

Note: showslavestatus descriptionMariaDB[(none)]>showslavestatus\G***************************1.row***************************Slave_IO_State:WaitingformastertosendeventMaster_Host:10.19.90.197Master_User:rootMaster_Port:3306Connect_Retry:60Master_Log_File:master-bin.000002Read_Master_Log_Pos:609Relay_Log_File:relay-bin.000006Relay_Log_Pos:818Relay_Master_Log_File:master-bin.000002Slave_IO_Running:YesSlave_SQL_Running:YesReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:SHOWSLAVESTATUS returns the following fields:Slave_IO_StateCopy the State field output by SHOWPROCESSLIST. SHOWPROCESSLIST is used for subordinate I/O threads. If the thread is trying to connect to the master server and is waiting for time to come to the master server or is connecting to the master server, this statement will notify youMaster_UserThe current user used to connect to the master server.Master_PortThe current master server interface.Connect_Retry–master-connect-Current value of the retry OptionMaster_Log_FileThe name of the binary log file of the Active Server currently being read by the I/O thread.Read_Master_Log_PosThe position that the I/O thread has read in the binary log of the current master server.Relay_Log_FileName of the relay log file currently being read and executed by the SQL thread.Relay_Log_PosThe location where the SQL thread has read and executed the current relay log.Relay_Master_Log_FileThe name of the binary log file of the master server that is executed by the SQL thread and contains most recent events.Slave_IO_RunningWhether the I/O thread is started and successfully connected to the master server.Slave_SQL_RunningWhether the SQL thread is started.Replicate_Do_DB,Replicate_Ignore_DBUse-replicate-do-db and-replicate-ignore-Database List specified by the db option.Replicate_Do_Table,Replicate_Ignore_Table,Replicate_Wild_Do_Table,Replicate_Wild_Ignore_TableUse-replicate-do-table,–replicate-ignore-table,–replicate-wild-do-tableAnd the-replicate-wild-ignore_table option specifies the list of tables.Last_Errno,Last_ErrorThe number of errors and error messages returned by most recently executed queries. The number of errors is 0 and the message is a null string, which means "no error ". If the value of Last_Error is not null, it will also be displayed as a message in the error log of the slave server.For more information, see: http://zhumeng8337797.blog.163.com/blog/static/10076891420115732244591/Or google, Keyword: "showslavestatus"

4. Start the slave synchronous connection service:

MariaDB[(none)]>startslave->;QueryOK,0rowsaffected,1warning(0.00sec)

5. view the slave node verification file:

6. view the synchronization status of the slave node:

4. Create a database on the master node and test whether the Server Load balancer is synchronized. These steps are not demonstrated. If you try them yourself, it will be OK,

5. Here I will explain how to synchronize existing data in mysql master-slave synchronization.

Problem: the original 197 server was originally a single SQL Server. The master-slave synchronization just created cannot synchronize the previous databases and data. I will explain this question to you.

Solution:

The host opens two shell windows, one for mysql and the other for shell.

1. Write blocking on the master host

MariaDB[(none)]>FLUSHTABLESWITHREADLOCK;QueryOK,0rowsaffected(0.00sec)

MariaDB[zentao]>showmasterstatus;+-------------------+----------+--------------+------------------+|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|+-------------------+----------+--------------+------------------+|master-bin.000002|2040|||+-------------------+----------+--------------+------------------+

2. Another shell exports the database of the master host

mysqldump-uroot-p--opt-Rzentao>/opt/zento-0412.sql

3. Unlock the lock

MariaDB[(none)]>UNLOCKTABLES;

4. Synchronize exported data

scp/opt/zentao-0412.sqlroot@10.19.90.111:/opt/

5. slave host

Stop slave

MariaDB[(none)]>stopslave;QueryOK,0rowsaffected(0.03sec)

6. Create a mysql database

createdatabasezentao;

7. Import Data

mysql-uroot-p'Password'zentao</opt/zentao-0412.sql

8. Start slave

MariaDB[(none)]>resetslave;QueryOK,0rowsaffected(0.00sec)

Vi. Verification

Logon: The web end connected to the master database. A new user is created in the background, as shown below:

Log on to the master database and check whether data is written to the user table;

Log on to the slave database and check whether the corresponding table has data synchronization;

I have already synchronized it here. I am too tired to write it ......

Note: It is estimated that it is okay to create a data synchronization record in the master database when synchronizing the database after the database is created. However, if you modify a data record, restart slave and view Last_ SQL _Error in show slave status: 1062 error. modify my. add the cof configuration file

Slave-skip-errors = 1062,After checking, the data is synchronized. Remove the slave-skip-errors = 1062 in the configuration and restart the database. when updating the master database table and viewing the data of the slave database, everything is normal. OK.
This article is from "A Jun blog" blog, please be sure to keep this source http://ssc4469.blog.51cto.com/6315913/1631627