MySQL configuration master-slave database

1. Purpose

1.1 Implementing Data Backup

1.2 Project Access enables read-write separation, improved speed of access and operation of data < read/write separation benefits >

2. Background

This time the master-slave configuration mainly realizes the change of main library data can be synchronized to from the library;

The main library for this trial is liunx<ubuntu4.8.2> MySQL 5.6.19, from the library for the WINDOWS10 MySQL 5.6.24;

3. Preparation of knowledge

3.1 The configuration file for MySQL under Liunx is:

/etc/mysql/my.conf

3.2 The configuration file for MySQL under Windows is:

Mysqlpath/my.ini

3.3 The path and suffix of the configured files are different in different system environments;

3.4 In the LIUNX system environment, the command to start the MySQL service shutdown restart is as follows:

//Start service //Close service // restart

3.5 You need to restart the service and view the results each time you modify the configuration file

4. Configuring the master-slave database

4.1 Open 3306 port, can allow remote access to the main library port;

A. First see if the port is open

Netstat-an|grep 3306

is open data display, if not open will appear 127.0.0.1 data, if not open, you need to/etc/mysql/my.conf

#bind-address = 127.0.0.1//Logoff Example bind-address = 127.0.0.1

Restart the service to see if the port is open;

B. Setting permissions for remote access to the MySQL database

Enter the main library using the SQL command to set permissions as follows:

Mysql>show databases;+--------------------+| Database           |+--------------------+|Information_schema||Cmdb||Mysql||Performance_schema||Test|+--------------------+Rowsinch Set(0.00sec) MySQL>  UseMySQL;DatabaseChangedmysql> Update User SetHost='%' where User='Root'; ERROR1062(23000): Duplicate Entry'%-root'  for Key 'PRIMARY' //Although the error, but the actual update success;

Alternative mysql>grant all privileges on * * to ' root ' @ '% ' identified by ' xxxxxx ';

　　　　

Open another terminal and execute the following command:

[[email protected] ~] - u rootmysql > Delete  from User where User = "' ; MySQL > Privileges ; #刷新权限mysql >\q

To restart the service, perform the following command to view the permissions given:

mysql> - -

mysql>  Use

mysql>userfromuser;

The following data can be obtained:

The% in host indicates that it can be accessed remotely using the root account;

4.2 Configuring the MySQL master-slave configuration file

Main Library: 192.168.0.175

From library: 192.168.0.170

A. Main Library configuration </etc/mysql/my.conf>

　　　　* The added configuration must be within the [mysqld] range or the service cannot be started
[Mysqld] log-bin=mysql-bin server-id=1 binlog-do-db = CMDB//database to be synchronized #binlog-ignore-db = NODB//database that does not need to be synchronized

After modification, restart the service to view the main library settings as follows:

Mysql>show master status;+---------------+----------+--------------+------------------+| File          |Position|binlog_do_db|binlog_ignore_db|+---------------+----------+--------------+------------------+|Binlog.000001 |     1304 |Cmdb|                  |+---------------+----------+--------------+------------------+Rowinch Set(0.00Sec

B. Configuring i<mysqlpath/my.ini> from the Library

To set the configuration file:

[mysqld]server-id=2//server ID, server-idmaster-host=192.168.0.175//main library IPMASTER-USER=REP1//5.5-master-password different from the main library =test123456//5.5-master-port=3306//5.5-replicate-do-db=cmdb//5.5-......

Configuration files In addition to Server-id, the MySQL 5.5+ version needs to set the remaining properties on the command line:

Mysql>Change MASTER to     -Master_host='Master_host_name',     -Master_user='Replication_user_name',     -Master_password='Replication_password',     -Master_log_file='Recorded_log_file_name',//data needs to be consistent with the data in Show Master status -Master_log_pos=Recorded_log_position;//data needs to be consistent with the data in Show Master status

Start the slave process

MySQL>0 rows affected (0.04 sec)

　　

Check the status of the slave, if the following two key values are yes, it means that the configuration is correct:

Slave_io_running:yes Slave_sql_running:yes

C. Copying the existing data from the main library to the library

ca. command replication

Main Library:

MySQL>withread lock; // Stop Data Update operations for the main library

To open a new terminal:

[[email protected] ~]# mysqldump-Uroot-Ptest123 CMDB>Cmdb.sql//generate a backup of the primary database (export database)　　　　[[email protected] ~]# SCP Cmdb.sql Root@192.168.0. the:/Root/ // upload backup files to MySQL from library >Unlock tables;//Main Library unlocked

From library:

Mysql>Slave stop;// stop slave mysql from library> Create DatabaseCmdbdefaultCharSet UTF8;//New database CMDB[[email protected] ~]# MySQL-Uroot-Ptest123 CMDB<Cmdb.sql// Import data mysql>show databases;// View the database and data already available from the library +--------------------+| Database           |+--------------------+|Information_schema||Cmdb||Mysql||Performance_schema||Test|+--------------------+

CB. Using tools <Navicat> copy directly from the main library to the library

D. When the master library data is changed, it is automatically updated from the library.

5. Follow-up questions

After modifying the data from the library, it will result in the synchronization of the main library from the library error, unable to synchronize the data to the main library;

Solution: Reconfigure the data from the library, re-import the data, and restart the service.

6. Project configuration read/write separation

Shift: Http://www.cnblogs.com/springlight/p/5905720.html

Resources:

1.http://www.cnblogs.com/zhoujie/p/mysql1.html

2.http://dev.mysql.com/doc/refman/5.6/en/replication-howto.html

3.http://www.linuxdiyf.com/linux/15206.html

4.http://www.cnblogs.com/linjiqin/p/5270938.html

The ability is limited, the document may have the improper place, you correct.

MySQL configuration master-slave database