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

17mysql replication

=

Benefits of using mysql replication

Avoid failing to provide services after the failure of the main database

Avoid locking during the backup of the main database, and synchronize the backup on the cluster database.

Keep multiple copies of data to avoid data loss

The cluster library provides read function to disperse the pressure of the main library.

Basic principles:

After initialization to ensure the same data, the main library keeps track of database changes through binary logs.

Replay the changes in the data in the master library according to the same log from the slave library

-

Configuration:

Step one:

The authorized slave library can connect to the master library and update it

This can be done on the master library by creating an account in the slave library or using grant replication

Grant replication slave on *. * to repadmin@slave identified by 'xxxxx'

Step 2:

Copying from the master library to the slave library is actually initialization to ensure consistency between the two sides

Step 3:

Configure the master library and give the master library a copy id number. The master and slave id numbers must be unique.

At the same time, you need to configure the main library to use binary log files.

Server-id=1

Log-bin=binary_log

Step 4:

Shut down and restart the main library for the new settings to take effect

Step 5:

Log in to the slave library, given a copy id number from the library and modify other parameters

Server-id=2

Master-host=master

Master-user=repadmin

Master-password=xxxxx

Step 6:

Restart the slave library and the server after modifying the slave library to make it effective

The slave library will create a master.info file in the data directory to record information about replication

-

Manage the replication process

Change master command

Change master to

Master_host='master_name'

Master_user='slave_name'

Master_password='slave_password'

Master_port='prot_number'

Master_log_file='binlog_filename'

Master_connect_rettry='value'

Relay_log_file='relaylog_filename'

Relay_log_pos='value'

The meaning of each parameter:

Master_host master server hostname

The user name that master_user uses when connecting to the primary server

The password used by master_password to connect to the main library

The port used by master_port to connect to the main library

The binary log file name of the main library read at the beginning of master_log_file replication

The location of the binary log file of the main library read at the beginning of master_log_pos replication

Number of seconds to wait between master_connect_rettry connection operations

The name of the slave relay log executed when relay_log_file starts replication

The location of the slave library relay log performed when relay_log_pos= starts replication

-

Purge master command

Delete the hot log on the main library

Purge master logs to binary-log

Purge master logs before date

Example:

Mysql > purge master logs to bin_log.999

Mysql > purge master logs to now ()

-

Start slave command

Start resuming replication

Show slave status can supervise the activities from the library.

-

Stop slave command

Terminate or end replication

Information provided by show slave status

The number of times connect_regry attempted to connect to the main library

The location of the binary log of the read_master_log_pos main library

Relay_log_file current Relay Log File

Relay_log_pos current relay log file location

The size of all relay files in relay_log_space

-

Show slave hosts command

Server_id copy id from the library

Replication id of master_id master library

Hostname of the host slave library

Connection Port of port Slave Library

-

Show processlist

State contains information about what the server is processing

Db thread database

The current statement executed by the command thread

Time for time thread execution

-

Load data from master

Get data from the library from the main library

But there are some restrictions.

-

Show binlog events

Show log events

Log_name binary file name

Location of events in the pos log

Event_type event type

-

Show master status

File binary update log file name

The current location of position in the binary log being written by the main library

List of databases recorded in the binlog_do_db binary update log

List of databases ignored and not recorded in the binlog_ignore_db binary update log

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.