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:
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