Detailed steps for implementing mysql hot backup in linux (mysql master-slave replication) _ MySQL

Detailed steps for implementing mysql hot backup in linux (mysql master-slave replication) bitsCN.com

Role of the master and slave:

1. it can be used as a backup method.

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

MySQL master-slave backup principle:

Mysql master-slave replication requires at least two Mysql services. of course, Mysql services can be distributed on different servers or multiple services can be started on one server.

If you want to configure it as the same platform, you must select two different prefix = paths during installation and the ports cannot be the same when you enable the server.

(1) first, ensure that the Mysql versions on the master and slave servers are the same. (the principle of master and slave servers is that the MYSQL versions must be the same. if not, at least the MYSQL version of the slave server must be higher than the MYSQL version of the master server)

(2) on the master server, set an account for the SLAVE database and grant permissions using replication slave, for example:

Mysql> grant replication slave on *. * TO 'slave001' @ '192. 168.0.99 'IDENTIFIED

'123 ';

Query OK, 0 rows affected (0.13 sec)

[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.

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

Lab environment preparation:

OS: CentOS5.4

Mysql: Mysql-5.0.41.tar.gz
Auxiliary tool: SSH Secure Shell Client

Two test IPs and servers:

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 and configuration steps:

1. first, allocate disk partitions in Linux to keep enough backup space for the MySQL database.

[Root @ vps mysql] # df-h
Filesystem Size Used Avail Use % Mounted on
/Dev/simfs 30G 2.0G 29G 7%/

2. install the MySQL database:

1> Upload mysql-5.0.41.tar.gz to the home directory of Linux through SSH

2> create MySQL Users and Groups:

# Groupadd mysql
# Useradd-g mysql

3> decompress the mysql-5.0.41.tar.gz source package

# Cd/usr/local/sofrware
# Tar zxvf Mysql-5.0.41.tar.gz

4> go to the source code directory for compilation and installation

# D/home/Mysql-5.0.41
#./Configure -- prefix =/usr/local/mysql -- with-charset = gbk | note: configure the Mysql installation path and support Chinese characters
# Make | note: Compile
# Make install | note: compile and install

5> replace the/etc/my. cnf file, enter the source code package, and execute the command

# D/home/Mysql-5.0.41
# Cp support-files/my-medium.cnf/etc/my. cnf

6> after completing the preceding operations, initialize the database and enter the installed mysql directory.

# Cd/usr/local/mysql
# Bin/mysql_install_db -- user = mysql | Note: -- user = mysql initializes the table and requires mysql Users

7> set access permissions for mysql and root users. First, go to the mysql directory.

# 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 users to access/usr/local/mysql/var
# Chgrp-R mysql/usr/local/mysql note: Set the mysql group to access/usr/local/mysq

8> start mysql and enter the installed directory.

# Cd/usr/local/mysql
# Bin/mysqld_safe -- user = mysql &

9>
Modify the default password of the root user of the mysql database:

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

Disable mysql server

Cd/usr/local/mysql/bin
./Mysqladmin-u root-p shutdown

10> set mysql to be started upon startup and go to the source code directory.

# D/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. [OK]
Starting MySQL [OK]
[Root @ localhost mysql] #

MySQL is installed here.

3. configure the Replication function of MySQL5.0

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;

5. configure my. cnf of slave and add the following content:

Note:

1. if mysql is 5.5.3-m3

Add the following content under the [mysqld] field:

Server-id = 2

2. for version 5.0x

Add the following content under the [mysqld] field:

Server-id = 2

Log-bin = mysql-bin // This is the synchronized binlog. take your binlog as the standard.

Master-host = 172.29.141.112

Master-user = admin

Masters-password = 12345678

Master-port = 3306

Master-connect-retry = 60 // The time difference between the reconnection if the master server is disconnected;

Replicate-do-db = blog // the database to be synchronized. if this row is left blank, all databases are synchronized.

Replicate-ignore-db = mysql // database that does not need to be backed up

Log-slave-update

Slave-skip-errors

My mysql is 5.5.3. here is my slave 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-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_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_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 = 60

Slave-skip-errors = 1396

# Master-host = 172.29.141.112

# Master-user = admin

# Master-password = 12345678

# Master-ports = 3306

Server-id = 2

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

6. Prepare for configuring slave by checking the status of the master (on the master)

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, we can see that the binlog currently used by the master is binlog.000005 and position is 592, so 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 position of the binlog.000005 log file used by the current master.

It is easy to understand which binlog data row (location) the master is using ).

7. for mysql 5.5.3-m3, you need to configure the information related to the master after starting slave (configured on slave)

Note: this corresponds to step 6.

Mysql> stop slave;

Mysql> change master to master_host = '192. 29.141.112 ', master_user = 'admin', master_password = '000000', master_log_file = 'binlog. 100', master_log_pos = 172;
Mysql> change master to MASTER_CONNECT_RETRY = 60;

This is the same as the configuration of my. cnf in MySQL 5.0. it is configured as the content corresponding to the master.

It is mainly to configure slave to let slave know which position on which binlog of the master node to copy data. Therefore, you need to know the master's ip address, user_name, user_passwd, binlog, binlog_position, and how long it takes to connect to the master.

8. enable slave

Mysql> start slave;

9. remove the read-only master restriction and perform tests.
Mysql> unlock tables;

Mysql> use blog;

Mysql> create longxibendi (a int, B int );


10. View on slave

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 |

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

12 rows in set (0.00 sec)


You can see that the operation is successful !!

11. during the configuration process, you can use show slave status/G; on slave

View server load balancer replication

12. what problems may be caused by firewall problems?

/Etc/init. d/iptables stop to disable the firewall on the master, or configure the firewall accordingly.

Common errors and solutions:

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

This error occurs because the mysql server is not started.

Previously, I configured master-slave according to 5.0x, and then started slave. this error will be reported when I connect to slave.

The reason was that mysql slave was not started, and then the error log was queried and the following fields were found.

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, I know that the parameter my. cnf in master-connect-retry = 60 has a problem. later I searched the internet and found that mysql5.5.3-m3 does not support this parameter,

Then I commented out the parameter and found that the parameter master-host is not supported. it can be seen from the ERROR field above. Later, we know that 5.5.3-m3 cannot be configured as 5.0.x.

You do not need to configure the master information from my. cnf. of course, server-id is required. For other information, log on to the mysql server configuration in the command line.

In fact, the role of server-id is

First, identify and distinguish different slave; second, prevent ring backup

2. Last_Error: Last_ SQL _Error: and other errors

This is obtained from running show slave status/G; on slave. The most fundamental reason for this problem is that the slave does not correspond to the binlog and position of the current master.

That is to say, the master binlog transmitted by slave does not correspond to the binlog and the number of rows of binlog that the current master is using.

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

This is because of firewall problems, so it is OK to disable the firewall with/etc/init. d/iptables stop.

4. ERROR 2013 (HY000): Lost connection to MySQL server during query.
Add skip-name-resolve to [mysqld] of/ect/my. cnf

BitsCN.com