MySQL 5.5.3-m3 synchronization and Master/Slave backup in CentOS 5.5

************* *************

I. Role of master and slave:

1. It can be used as a backup method.

2. read/write splitting to relieve the pressure on a database

II. Environment:

OS CentOS5.5

DB MySQL5.5.3-m3

To install CentOS5.5, see

Install MySQL5.5.3-m3.

Iii. MySQL master-slave backup Principle

Binlog is provided on the master,

Slave extracts the binlog from the master through the I/O thread and copies it to the slave relay log.

Slave reads the binlog from the slave relay log through the SQL thread, and then resolves it to the slave.

4. Master-slave Replication

Synchronization between the master and slave is required, because the author's database data volume is not large, so there is no need to consider too much, directly put

The data on the master node is copied to the slave, but if the data volume is large, such as a system like taobao

Data Synchronization is also very rare, and a complete solution is required. If you are interested, please refer to this article.

Http://www.taobaodba.com/html/564_%E5%A2%9E%E9%87%8F%E6%97%A5%E5%BF%97%E8%BF%AD%E4%BB%A3%E5%90%8C%E6%AD%A5%E5%92%8C%E9%98%BF%E5%9F%BA%E9%87%8C%E6%96%AF%E6%82%96%E8% AE %BA.html

************* **************************************

1. Set the master to read-only.

Mysql> flush tables with read lock;

2. Replace the data folder in slave with the data folder in master

For example, use tar zcvf mysql_data.gz/media/raid10/mysql/3306/data

Then mv mysql_data.gz/media/raid10/htdocs/blog/wordpress/

Because my/media/raid10/htdocs/blog/wordpress/is the main directory of Nginx

Therefore, you can use wget to download the file on slave, decompress the file, and overwrite the data file on slave.

Note: It is best to back up the source file before overwriting.

3. Configure my. cnf for the master and add the following content:

Add the following fields in the [mysqld] configuration section:

Server-id = 1

Log-bin =/media/raid10/mysql/3306/binlog // enter the absolute path name of your binlog.

Binlog-do-db = blog // the database to be synchronized. If no row exists, all databases are synchronized.

Binlog-ignore-db = mysql // ignored Database

Here is my. cnf configuration file

[Client]

Character-set-server = utf8

Port = 3306

Socket =/tmp/mysql. sock

[Mysqld]

Character-set-server = utf8

Replicate-ignore-db = mysql

Replicate-ignore-db = test

Replicate-ignore-db = information_schema

User = mysql

Port = 3306

Socket =/tmp/mysql. sock

Basedir =/usr/local/webserver/mysql

Datadir =/media/raid10/mysql/3306/data

Log-error =/media/raid10/mysql/3306/mysql_error.log

Pid-file =/media/raid10/mysql/3306/mysql. pid

Open_files_limit = 10240

Back_log = 600

Max_connections = 5000

Max_connect_errorrs = 6000

Table_cache = 614

External-locking = FALSE

Max_allowed_packet = 16 M

Sort_buffer_size = 1 M

Join_buffer_size = 1 M

Thread _ cache_size = 300

# Thread_concurrency = 8

Query_cache_size = 20 M

Query_cache_limit = 2 M

Query_cache_min_res_unit = 2 k

Default-storage-engine = MyISAM

Thread_stack = 192 K

Transaction_isolation = READ-COMMITTED

Tmp_table_size = 20 M

Max_heap_table_size = 20 M

Long_query_time = 3

Log-slave-updates

Log-bin =/media/raid10/mysql/3306/binlog

Binlog-do-db = blog

Binlog-ignore-db = mysql

Binlog_cache_size = 4 M

Binlog_format = MIXED

Max_binlog_cache_size = 8 M

Max_binlog_size = 20 M

Relay-log-index =/media/raid10/mysql/3306/relaylog

Relay-log-info-file =/media/raid10/mysql/3306/relaylog

Relay-log =/media/raid10/mysql/3306/relaylog

Expire_logs_days = 30

Key_buffer_size = 10 M

Read_buffer_size = 1 M

Read_rnd_buffer_size = 6 M

Bulk_insert_buffer_size = 4 M

Myisam_sort_buffer_size = 8 M

Myisam_max_sort_file_size = 20 M

Myisam_repair_threads = 1

Myisam_recover

Interactive_timeout = 120

Wait_timeout = 120

Skip-name-resolve

# Master-connect-retry = 10

Slave-skip-errors = 1396

# Master-host = 192.168.1.2

# Master-user = username

# Master-password = password

# Master-ports = 3306

Server-id = 1

Innodb_additional_mem_pool_size = 16 M

Innodb_buffer_pool_size = 20 M

Innodb_data_file_path = ibdata1: 56 M: autoextend

Innodb_file_io_threads = 4

Innodb_thread_concurrency = 8

Innodb_flush_log_at_trx_commit = 2

Innodb_log_buffer_size = 16 M

Innodb_log_file_size = 20 M

Innodb_log_files_in_group = 3

Innodb_max_dirty_pages_pct = 90

Innodb_lock_wait_timeout = 120

Innodb_file_per_table = 0

# Log-slow-queries =/media/raid10/mysql/3306/slow. log

# Long_query_time = 10

[Mysqldump]

Quick

Max_allowed_packet = 32 M

4. Add a synchronization account for the slave machine on the master machine

Mysql> grant replication slave on *. * to 'admin' @ '192. 29.141.115 'identified by '123 ';

Mysql> flush privileges;

1 2 3 4 Next Page