Centos6.5 simple explanation based on mariadb10.x master-slave replication High Availability

Purpose of Master-slave replication:

MySQL server stability to improve, to avoid the single MySQL server downtime after the impact of the entire business, when the problem of downtime, you can immediately promote the slave to a new primary server. This allows for high availability of SQL redundancy.

First, the demonstration 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 machine slave:10.19.90.111

Second, (Note: SQL installation does not show, please refer to http://ssc4469.blog.51cto.com/6315913/1627739 in the Mariadbab installation , In addition I have shown here the database version consistent)

Master server configuration:

1. Stop the Service

#/etc/init.d/mysqld Stop

2, modify the MySQL configuration file,/etc/my.cnf, I only modified two parameters here, one is the MySQL log path, one is Server-id 1

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/5F/DF/wKiom1UqaAvj_n6_AAGjCSjo0C4625.jpg "title=" 11.png "alt=" Wkiom1uqaavj_n6_aagjcsjo0c4625.jpg "/>

3, create the Binlog directory, modify the genus Group

# mkdir-pv/mydata/binlogs/# Chown-r mysql:mysql/mydata/binlogs/

4. Start the MySQL service to view the boot information

#/etc/init.d/mysqld Start

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/5F/DC/wKioL1Uqas6xyrrzAAEz9NPQLkc242.jpg "title=" 2.png " alt= "Wkiol1uqas6xyrrzaaez9npqlkc242.jpg"/>5, login MySQL authorized remote user

MariaDB [(None)]> Grant replication Slave,replication Client on * * to ' user ' @ ' 10.19.90.111 ' identified by ' password ';   Note: The user password must be the current SQL created user and password mariadb [(none)]> flush privileges; Refresh the MySQL system permissions related table

Slave Server configuration:

1. Stop the Service

#/etc/init.d/mysqld Stop

2, modify the MySQL configuration file,/etc/my.cnf, I only modified two parameters here, one is to enable the MySQL relay log path, one is Server-id 2

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/5F/E3/wKiom1UqayOBUdMuAADbvLtDRy4313.jpg "title=" 111. PNG "alt=" wkiom1uqayobudmuaadbvltdry4313.jpg "/>

3, create the Binlog directory, modify the genus Group

# mkdir-pv/mydata/binlogs/# Chown-r mysql:mysql/mydata/binlogs/

4. Start the MySQL service to view the boot information

#/etc/init.d/mysqld Start

5. Connect to MySQL and check the status of the trunk log:

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

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/5F/E1/wKioL1UqbbGCjvEvAAJR3q6MHOU033.jpg "title=" 22.png "alt=" Wkiol1uqbbgcjvevaajr3q6mhou033.jpg "/>

6. View verification (no files at this time)

# Ls/mydata/relaylogs

7. Log in to MySQL authorized remote user

MariaDB [(None)]> Grant replication Slave,replication Client on * * to ' user ' @ ' 10.19.90.197 ' identified by ' password ';   Note: The user password must be the current SQL created user and password mariadb [(none)]> flush privileges; Refresh the MySQL system permissions related table

Third, view master, slave node status and enable slave node

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/5F/EC/wKiom1UqbunBpekKAANwS9Sy0us186.jpg "title=" 44.png "alt=" Wkiom1uqbunbpekkaanws9sy0us186.jpg "/>

2. Maser View Log Status:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/5F/FD/wKiom1Uqc3yDzmZqAAEWlh3-y44770.jpg "title=" 1111. PNG "alt=" wkiom1uqc3ydzmzqaaewlh3-y44770.jpg "/>

3. Slave node view synchronization status (Note: The picture is already synchronized in the state, actually just created,Slave_io_running:no,slave_sql_running:no, mainly look at these two parameters )

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/5F/FF/wKiom1Uqc-rR9dNlAAP5JCRVh-E752.jpg "title=" 55.png "alt=" Wkiom1uqc-rr9dnlaap5jcrvh-e752.jpg "/>

Note:show slave status  explanation   mariadb [(none)]> show slave status\g*  1. row ***************************                slave_io_state: waiting for master  to send event                   Master_Host: 10.19.90.197                   Master_User: root                   master_port: 3306                 connect_retry:  60              master_log_file:  master-bin.000002          read_master_log_pos: 609                Relay_Log_File: relay-bin.000006                 Relay_Log_Pos:  818        relay_master_log_file: master-bin.000002              Slave_IO_Running: Yes             Slave_SQL_Running: Yes               Replicate_Do_DB:            Replicate_Ignore_DB:             replicate_do_table:        replicate_ignore_table:        replicate_wild_do_table:       show slave  The status returns the following fields: Copy of the State field of the slave_io_stateshow processlist output. The show processlist is used for slave I/O threads. If the thread is trying to connect to the primary server, is waiting for the time to come to the host server, or is connecting to the primary server, this statement notifies you that Master_user is the current user that is used to connect to the primary server. Master_port the current primary server interface. The current value of the Connect_retry–master-connect-retry option master_log_filei/o the name of the primary server binary log file that the thread is currently reading. Read_master_log_pos the location where the I/O thread has been read in the current primary server binary log. The name of the trunk log file that the Relay_log_filesql thread is currently reading and executing. Relay_log_pos the location in the current trunk log where the SQL thread has been read and executed. Relay_master_log_file the name of the primary server binary log file that is executed by the SQL thread that contains most recent events. Whether the SLAVE_IO_RUNNINGI/O thread was started and successfully connected to the primary server. Whether the Slave_sql_runningsql thread is started. replicate_do_db,replicate_ignore_db the list of databases specified using the –REPLICATE-DO-DB and –REPLICATE-IGNORE-DB options. Replicate_do_table,replicate_ignore_table,replicate_wild_do_table,replicate_wild_ignore_table Use – The list of tables specified by the replicate-do-table,–replicate-ignore-table,–replicate-wild-do-table and –replicate-wild-ignore_table options. Last_errno,last_error is the number of errors and error messages returned by most recently executed queries. The number of errors is 0 and the message is an empty string that means "no error". If the Last_error value is not a null value, it is also in the subordinate serverDisplayed as a message in the error log. For more information, please refer to: http://zhumeng8337797.blog.163.com/blog/static/10076891420115732244591/or Google, Keywords: "Show slave  status "

4, start slave synchronous connection service:

MariaDB [(None)]> start slave; Query OK, 0 rows affected, 1 Warning (0.00 sec)

5, slave node verification file view:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/60/02/wKioL1Uqd3iypXKwAABw3Y0Dw5A193.jpg "title=" 6666. PNG "alt=" wkiol1uqd3iypxkwaabw3y0dw5a193.jpg "/>

6, slave node synchronization Status View:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/60/08/wKiom1UqdnWCNicjAACuo9OLgaY181.jpg "title=" 77.png "alt=" Wkiom1uqdnwcnicjaacuo9olgay181.jpg "/>

Four,master to create a library, test slave is synchronized, these steps are not in the demonstration, everyone to try on the OK, very simple,

Five, I say here, MySQL master-slave synchronization, how to synchronize the existing data.

Problem: Originally 197 This server is the first single SQL Server, the master-slave synchronization just created, can not synchronize the previous library and data, let me tell you about this problem.

Workaround:

Host open two shell windows, one into MySQL, one is shell

1, master host block write operation

MariaDB [(None)]> FLUSH TABLES with READ LOCK; Query OK, 0 rows Affected (0.00 sec)

MariaDB [zentao]> Show Master status;+-------------------+----------+--------------+------------------+| File | Position | binlog_do_db | binlog_ignore_db |+-------------------+----------+--------------+------------------+|     master-bin.000002 |              2040 |                  | |+-------------------+----------+--------------+------------------+

2. Another shell exports the corresponding database of master host

Mysqldump-u root-p--opt-r Zentao >/opt/zento-0412.sql

3. Unlock the lock you just made

MariaDB [(None)]> UNLOCK TABLES;

4. Synchronizing the exported data

scp/opt/zentao-0412.sql [Email protected]:/opt/

5. Slave host

Stop slave

MariaDB [(none)]> stop slave; Query OK, 0 rows affected (0.03 sec)

6. Create MySQL Library

Create Database Zentao;

7. Import data

Mysql-uroot-p ' password ' Zentao </opt/zentao-0412.sql

8, from Kai Slave

MariaDB [(none)]> reset slave; Query OK, 0 rows Affected (0.00 sec)

VI. Verification

Login: The Web side of the master database connection, create a new user in the background as follows:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/60/39/wKioL1UqhPrjJAynAADXMlA4XlY181.jpg "title=" 88.png "alt=" Wkiol1uqhprjjaynaadxmla4xly181.jpg "/>

Log in to the master database to see if the user table has data written;

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/60/3D/wKioL1UqhZaB3XDiAAFZsmBMkyQ007.jpg "title=" 99.png "alt=" Wkiol1uqhzab3xdiaafzsmbmkyq007.jpg "/>

Log in to the slave database to see if the corresponding table has data synchronization.

I have synchronized here, too lazy to write, exhausted me ...

Special note: estimated that someone created after synchronizing the database, such as the main library to create a data synchronization is OK, but modify a data can not sync, restart slave in show slave status, you will see last_sql_error:1062 error, Here, please modify the MY.COF configuration file from the library, add the

Slave-skip-errors = 1062, in the view, the data is synchronized, remove the configuration of slave-skip-errors = 1062, restart the database, in the Update Main library table, in view from the library corresponding data, Everything is fine. OK, that's it. Paralysis exhausted me ....

PS: Highly available scripts write them down in two days.

This article is from the "Chun Blog" blog, please be sure to keep this source http://ssc4469.blog.51cto.com/6315913/1631627

Centos6.5 simple explanation based on mariadb10.x master-slave replication High Availability