Shulou(Shulou.com)06/01 Report--

1. Mysql master-slave synchronization (for the separation of read and write, MMM high availability cluster as the basis)

Master library configuration: IP address of the master library: 192.168.4.111

1. First, the user is authorized to achieve synchronization, and the user name of the master database is connected to the slave library:

Grant replication slave on *. * to tongbu@ "%" identified by "123456"

/ / authorize tongbu this user has the right to copy on all slave libraries and set the password

2. Enable binlog log: vim / etc/my.cnf

Server_id=111 / / specify the number

Log-bin=master111 / / specify the log file name and enable binlog logging

Binlog-format= "mixed" / / Log Type

3. Restart the database service:

4. View the binlog log file in use: show master status\ G

Slave library configuration: slave library IP address: 192.168.4.112

1. Check whether you are a master-slave database.

Show master status; show slave status

2. Test whether the authorized user of the main database is valid, and see if you can log in.

3. Add server_id=112 to the configuration file and restart the service

4. Under the mysql command line, change the configuration and configure the information related to the main library.

Change master to

-> master_host='192.168.4.111', / / address of the main library

-> user name of the master_user='tongbu', / / connection

-> master_password='123456', / / password

-> master_log_file='111-bin.000001', / / log file name

-> master_log_pos=154; / / offset

5. Start the slave service: start slave

6. Show slave status\ G / / check the status of slave and make sure that the following two lines are yes

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

7. Test the synchronization effect:

On the master server, create libraries, tables, insert records, and see if the data is the same on the slave server.

2. Some information about log files

1. Several common log files

112-relay-bin.000006 / / Relay log file

112-relay-bin.index / / Relay log file index

Relay-log.info / / Relay log

Master.info / / main library information

2. How to restore from the library to a separate server:

Stop slave; reset slave all

3. Some information about threads and programs

IO thread: record the binlog log contents of the master library to the local relay-binlog log

Sql thread: writes the sql commands in the relay-binlog log to the local library

Show processlist; Binlog Dump / / look at the program running on the host, and then you can see the binlog dump program

Show processlist; Connect / / you can view programs running from the library

Third, common parameters of master-slave synchronization (/ etc/my.cnf)

1. Main library: binlog_do_db= database name / / libraries that only allow synchronization

Binlog_ignore_db= database name / / out-of-sync library

2. Slave library: enable cascading replication (log_slave_updates) configuration on the first slave library of master-slave structure

Synchronize individual databases (replicate_do_db= database name)

Out-of-sync library (replicate_ignore_db= database name)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.