Detailed steps for MySQL hot backup under Linux system (MySQL master-slave replication) _mysql

The role of Master and subordinate:

1. Can be used as a backup method

2. Used to achieve the separation of read and write, ease the pressure of a database

MySQL master-slave backup principle:

MySQL's master-slave replication is at least two MySQL services, of course, MySQL services can be distributed on different servers, can also start multiple services on a single server.

If you want to configure the same platform, note that when the installation, select two different prefix= path, while the server, the port can not be the same.

(1) First of all to ensure that the main server MySQL version of the same (the rule from the server's principle is that the MySQL version to the same, if not satisfied, at least from the server MySQL version must be higher than the main server MySQL version)

(2) on the primary server, set up an account from the database, using replication slave to grant permissions, such as:

Copy Code code as follows:

Mysql> GRANT REPLICATION SLAVE on *.* to ' slave001 ' @ ' 192.168.0.99 ' identified by

' 123456 ';

Query OK, 0 rows affected (0.13 sec)

[Principle]master provides Binlog,

Slave takes binlog from master via I/O thread and copies it to Slave's relay log

Slave reads the binlog from the slave's relay log through an SQL thread and resolves to slave

Master and slave reproduction premise

Master and slave need to sync, because the author's database data is small, so no need to consider too much, directly

Data on master is copied to the slave, but if it is a large amount of data, such as a system like Taobao

Experimental environment Preparation:

os:centos5.4

Mysql:mysql-5.0.41.tar.gz
Assistive tools: SSH Secure Shell Client

Two Test ip& servers:

Copy Code code as follows:

Master Server:192.168.1.2/linux Centos5.4/mysql 5.0
Slave Server:192.168.1.3/linux Centos5.4/mysql 5.0

Installation configuration steps:

1, first in the Linux environment to allocate a good disk partition to sufficient MySQL database backup space

Copy Code code as follows:

[Root@vps mysql]# Df-h
FileSystem Size Used Avail use% mounted on
/dev/simfs 30G 2.0G 29G 7%/

2, the MySQL database installation:

1> will upload mysql-5.0.41.tar.gz to the Linux system's home directory via the SSH tool

2> set up MySQL users and groups:

Copy Code code as follows:

#groupadd MySQL
#useradd-G MySQL MySQL

3> Extract mysql-5.0.41.tar.gz Source pack

Copy Code code as follows:

#cd/usr/local/sofrware
#tar ZXVF mysql-5.0.41.tar.gz

4> Enter source directory compile installation

Copy Code code as follows:

#cd/home/mysql-5.0.41
#./configure--prefix=/usr/local/mysql--WITH-CHARSET=GBK | Note: Configure the MySQL installation path and support Chinese
#make | Note: compiling
#make Install | Note: Compiling installation

5> Replace/etc/my.cnf file, enter source package, execute command

Copy Code code as follows:

#cd/home/mysql-5.0.41
#cp support-files/my-medium.cnf/etc/my.cnf

6> complete the above operation to initialize the database, into the installed MySQL directory

Copy Code code as follows:

#cd/usr/local/mysql
#bin/mysql_install_db--user=mysql | Note:--user=mysql initialization table and specify the MySQL user

7> set access permissions for MySQL and root users let's go to the MySQL directory first.

Copy Code code as follows:

#cd/usr/local/mysql
#chown-R root/usr/local/mysql Note: set ROOT to access/usr/local/mysq
#chown-R mysql/usr/local/mysql/var Note: set MySQL user access to/usr/local/mysql/var
#chgrp-R mysql/usr/local/mysql Note: set MySQL group to access/usr/local/mysq

8> start MySQL and go to the already installed directory

Copy Code code as follows:

#cd/usr/local/mysql
#bin/mysqld_safe--user=mysql &

9>
To modify the default password for the MySQL database super User root:

Copy Code code as follows:

/usr/local/mysql/bin/mysqladmin-u root password ' mysql '

Shutdown MySQL Server

Copy Code code as follows:

Cd/usr/local/mysql/bin
./mysqladmin-u root-p Shutdown

10> set boot on the MySQL, access to the source directory

Copy Code code as follows:

# cd/home/mysql-5.0.41
# CP Support-files/mysql.server/etc/init.d/mysql

# chmod +x/etc/init.d/mysql
# chkconfig--level 345 MySQL on
# service MySQL Restart
Shutting down MySQL. Determine
Starting MySQL [OK]
[Root@localhost mysql]#

MySQL is ready to go here.

3, configure the MySQL5.0 copy (Replication) function

One. Set Master to read-only.

Mysql> flush tables with read lock;

Two. Replace the data folder in slave with the Data folder in master

Like using 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 home directory of Nginx.

So you can download the file on the slave, wget it, and then unzip it and overwrite the data file on the slave.

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

Three. Configure Master MY.CNF and add the following

Add the following fields to the [mysqld] configuration segment

Copy Code code as follows:

Server-id=1

Log-bin=/media/raid10/mysql/3306/binlog/binlog//Here Write your binlog absolute path name

Binlog-do-db=blog//database that needs to be synchronized, which means synchronizing all databases if there is no bank

Binlog-ignore-db=mysql//Ignored database

Here's my my.cnf configuration file.

Copy Code code as follows:

[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_errors = 6000

Table_cache = 614

external-locking = FALSE

Max_allowed_packet = 16M

Sort_buffer_size = 1M

Join_buffer_size = 1M

Thread_cache_size = 300

#thread_concurrency = 8

Query_cache_size = 20M

Query_cache_limit = 2M

Query_cache_min_res_unit = 2k

Default-storage-engine = MyISAM

Thread_stack = 192K

Transaction_isolation = read-committed

Tmp_table_size = 20M

Max_heap_table_size = 20M

Long_query_time = 3

Log-slave-updates

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

Binlog-do-db=blog

Binlog-ignore-db=mysql

Binlog_cache_size = 4M

Binlog_format = MIXED

Max_binlog_cache_size = 8M

Max_binlog_size = 20M

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

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

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

Expire_logs_days = 30

Key_buffer_size = 10M

Read_buffer_size = 1M

Read_rnd_buffer_size = 6M

Bulk_insert_buffer_size = 4M

Myisam_sort_buffer_size = 8M

Myisam_max_sort_file_size = 20M

Myisam_repair_threads = 1

Myisam_recover

Interactive_timeout = 120

Wait_timeout = 120

Skip-name-resolve

#master-connect-retry = 10

Slave-skip-errors = 1032,1062,126,1114,1146,1048,1396

#master-host = 192.168.1.2

#master-user = Username

#master-password = password

#master-port = 3306

Server-id = 1

Innodb_additional_mem_pool_size = 16M

Innodb_buffer_pool_size = 20M

Innodb_data_file_path = Ibdata1:56m:autoextend

Innodb_file_io_threads = 4

Innodb_thread_concurrency = 8

Innodb_flush_log_at_trx_commit = 2

Innodb_log_buffer_size = 16M

Innodb_log_file_size = 20M

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 = 32M

Four. Add a sync account number for slave machine on Master machine

Copy Code code as follows:

mysql> grant replication Slave on *.* to ' admin ' @ ' 172.29.141.115 ' identified by ' 12345678 ';

mysql> flush Privileges;

Five. Configure slave my.cnf and add the following

Attention:

1. If MySQL is a 5.5.3-m3 version, just

Add the following in the [Mysqld] field

server-id=2

2. If it is a 5.0x version, you need

Add the following in the [Mysqld] field

Copy Code code as follows:

server-id=2

Log-bin=mysql-bin//This is a synchronized binlog, specific to your binlog as the subject

master-host=172.29.141.112

Master-user=admin

master-password=12345678

master-port=3306

MASTER-CONNECT-RETRY=60//If the primary server is found disconnected, reconnect the time lag;

Replicate-do-db=blog//Synchronized database, no line written to synchronize all databases

Replicate-ignore-db=mysql//databases that do not need to be backed up

Log-slave-update

Slave-skip-errors

My MySQL is 5.5.3, here's my slave my.cnf config file.

Copy Code code as follows:

[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-do-db = Blog

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_errors = 6000

Table_cache = 614

external-locking = FALSE

Max_allowed_packet = 16M

Sort_buffer_size = 1M

Join_buffer_size = 1M

Thread_cache_size = 300

#thread_concurrency = 8

Query_cache_size = 20M

Query_cache_limit = 2M

Query_cache_min_res_unit = 2k

Default-storage-engine = MyISAM

Thread_stack = 192K

Transaction_isolation = read-committed

Tmp_table_size = 20M

Max_heap_table_size = 20M

Long_query_time = 3

Log-slave-updates

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

Binlog_cache_size = 4M

Binlog_format = MIXED

Max_binlog_cache_size = 8M

Max_binlog_size = 20M

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

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

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

Expire_logs_days = 30

Key_buffer_size = 10M

Read_buffer_size = 1M

Read_rnd_buffer_size = 6M

Bulk_insert_buffer_size = 4M

Myisam_sort_buffer_size = 8M

Myisam_max_sort_file_size = 20M

Myisam_repair_threads = 1

Myisam_recover

Interactive_timeout = 120

Wait_timeout = 120

Skip-name-resolve

#master-connect-retry = 60

Slave-skip-errors = 1032,1062,126,1114,1146,1048,1396

#master-host=172.29.141.112

#master-user = admin

#master-password = 12345678

#master-port = 3306

Server-id = 2

Innodb_additional_mem_pool_size = 16M

Innodb_buffer_pool_size = 20M

Innodb_data_file_path = Ibdata1:56m:autoextend

Innodb_file_io_threads = 4

Innodb_thread_concurrency = 8

Innodb_flush_log_at_trx_commit = 2

Innodb_log_buffer_size = 16M

Innodb_log_file_size = 20M

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 = 32M

Six. Prepare for configuration slave by looking at the status of Master (viewed on master)

Copy Code code as follows:

Mysql> Show Master status/g;

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect ...

Connection id:13

Current Database:blog

1. Row ***************************

file:binlog.000005

position:592

Binlog_do_db:blog

Binlog_ignore_db:mysql

1 row in Set (0.01 sec)

ERROR:

No query specified

From the above information, you can see that master now uses the Binlog is Binlog.000005,position is 592, then the following slave configuration must correspond to this.

In fact, binlog.000005 is the Binlog log file used by the current master

Position is the location of the binlog.000005 log file used by the current master

A simple understanding of which Binlog data row (location) the master is using.

Seven. If it is 5.5.3-m3 version of MySQL, you need to start slave, configure the information related to master (configured on the slave)

Note that this corresponds to the sixth step

Copy Code code as follows:

mysql> stop Slave;

mysql> Change Master to master_host= ' 172.29.141.112 ', master_user= ' admin ', master_password= ' 12345678 ', Master_log_ File= ' binlog.000005 ', master_log_pos=488;
mysql> change MASTER to master_connect_retry=60;

This is the same as the 5.0 configuration MY.CNF, configured to match Master's content

The main is to configure slave to let slave know where to copy data from which binlog on master. So you need to know the ip,user_name,user_passwd,binlog,binlog_position of master and how long to connect master at a time.

Eight. Open slave

Copy Code code as follows:

mysql> start slave;

Nine. Unlock master read-only limit and do test

Copy Code code as follows:

mysql> unlock tables;

mysql> use blog;

Mysql> Create Longxibendi (a int, b int);

10. View from slave

Copy Code code as follows:

mysql> use blog;

Mysql> Show tables;

+-----------------------+

| Tables_in_blog |

+-----------------------+

| Longxibendi |

| Wp_commentmeta |

| wp_comments |

| Wp_links |

| wp_options |

| Wp_postmeta |

| wp_posts |

| Wp_term_relationships |

| wp_term_taxonomy |

| wp_terms |

| Wp_usermeta |

| Wp_users |

+-----------------------+

Rows in Set (0.00 sec)

Can see the success!!

11. In the process of configuration, you can use show slave status/g; On the slave.

View replication for Slave

12. If there is any problem, it may be a firewall problem

/etc/init.d/iptables stop shuts down the firewall on master, or makes the appropriate configuration

Frequently encountered errors and solutions:

1.[mysql]error 2002 (HY000): Can ' t connect to the local MySQL server through socket '/var/lib/mysql/mysql.sock ' (2)

This error, there are many claims on the web, in fact, the direct reason is that the MySQL server did not start

Before I follow 5.0x configuration master-slave, then start slave, in connection slave, will report this error

Later found that the reason is that the MySQL slave did not start up, and then check the error log, found the following fields

Copy Code code as follows:

110505 01:55:20 mysqld_safe mysqld from PID File/media/raid10/mysql/3306/mysql.pid ended

110505 02:04:41 Mysqld_safe starting mysqld daemon with databases From/media/raid10/mysql/3306/data

Innodb:the InnoDB memory heap is disabled

Innodb:mutexes and Rw_locks use InnoDB ' s own implementation

110505 2:04:41 innodb:highest Supported file format is barracuda.

110505 2:04:41 InnoDB Plugin 1.0.6 started; Log sequence Number 44338

110505 2:04:41 [ERROR]/usr/local/webserver/mysql/libexec/mysqld:unknown variable ' master-connect-retry=60 '

110505 2:04:41 [ERROR] Aborting

110505 2:04:41 innodb:starting shutdown ...

110505 2:04:43 Innodb:shutdown completed; Log sequence Number 44348

110505 2:04:43 [note]/usr/local/webserver/mysql/libexec/mysqld:shutdown complete

110505 02:04:43 mysqld_safe mysqld from PID File/media/raid10/mysql/3306/mysql.pid ended

110505 02:07:44 Mysqld_safe starting mysqld daemon with databases From/media/raid10/mysql/3306/data

Innodb:the InnoDB memory heap is disabled

Innodb:mutexes and Rw_locks use InnoDB ' s own implementation

110505 2:07:44 innodb:highest Supported file format is barracuda.

110505 2:07:45 InnoDB Plugin 1.0.6 started; Log sequence Number 44348

110505 2:07:45 [ERROR]/usr/local/webserver/mysql/libexec/mysqld:unknown variable ' master-host=172.29.141.112 '

110505 2:07:45 [ERROR] Aborting

From the above error, know master-connect-retry=60 this my.cnf in the parameters of the problem, and later from the Internet search data, found that MYSQL5.5.3-M3 version does not support this parameter,

Then I commented out this parameter, and found that it does not support this parameter master-host, from the above error field can be seen. Later, I knew that 5.5.3-M3 could not be configured as 5.0.x.

It is not necessary to configure master-related information from MY.CNF, of course server-id is necessary. Other information, by landing the MySQL server configuration at the command line.

In fact, the role of Server-id is

First, identify, distinguish different slave, second, prevent the occurrence of ring backup

2.last_error:last_sql_error: etc Error

This is from slave, run show slave status/g; To get. The most fundamental reason for this problem is that slave does not correspond to the position of the current master's Binlog and Binlog

That is, the master binlog of the slave transfer does not correspond to the binlog that the current master is using and the number of rows Binlog.

3.[error] Slave I/o: ERROR connecting to master ' admin@172.29.141.112:3306 '-retry-time:60 retries:86400, Error_code:2 003

This is because of the firewall problem, so use/etc/init.d/iptables stop to shut down the firewall OK.

4. Encountered error 2013 (HY000): Lost connection to MySQL server during query errors.
Add Skip-name-resolve to [mysqld] in/ECT/MY.CNF