The MySQL internal replication function is to be built between two or more servers, which is implemented by setting the main-rope relationship between them. One is the primary server, and other as the secondary server. Hereinafter, how to configure two servers in detail, set one as the primary server, and the other is a sub-server. And describe the processing procedure for switching between them.
1 Procedure 1.1 Main Server Authorization Authorized sub-server can connect the primary server and can be updated. This is made on the primary server, creating a username and password for sub-server access. Enter the mysql command line
MySQL> Grant Replication Slave on *. * to 'username' @ 'slave-server-hostname' Identified by 'password'; replaced UserName, Slave-Server-Hostname, Password, based on actual conditions.
1.2 Data Replication Once the access license is established, the next is to copy data from the primary server to the sub-server. Can be from the MySQLDUMP tool.
# 住 表, prohibit Update. Mysql> Flush Tables with read lock;
# Export Data Windows for the command prompt.
Shell> MySQLDUMP -OPT DatabaseName> Backup.sql
#unlock
MySQL> Unlock Tables
Copy the backup database script backup.sql to the sub-service device.
You can also use the Load Data from Master statement to transfer the data of the primary server to the sub-server, but some restrictions are used. Note: During the execution of the data replication, make sure you cannot perform an update operation for the primary server.
1.3 Configuring the primary server to assign a Server-ID in the option configuration file, which must be a unique value within the range of 1 to 2 ^ 23-1. The Server-ID of the primary server and the secondary server cannot be the same.
In addition, you also need to configure the primary server, enable it to enable the binary log, that is, add the log-bin boot option in the option configuration file.
Option file example (not comment on # out of the #):
[MySQLD] # Unique value, does not with the second # log file in the same server-id = 3 # log file with binary_log is prefix, if not assigning the log-bin, the log file will be prefixed with # master-server-hostname as a prefix log-bin = Binary_log # log file Skip Database (optional Properties) BINLOG-IGNORE-DB = mysql # log file operation database (optional attribute) binlog-do-db = netbar
1.4 Restart the main server to configure the above option, restart the mysql service, and the new option will take effect. Now, all updates to information in the database will now be written into the log.
Note: If the primary server's binary log is enabled, the previous binary log should be backed up before it is restarted. After restarting, use the Reset Master statement to empty the previous log.
1.5 Configuring the sub-server Add the following parameters in the MySQL option configuration file on the sub-server.
[MySQLD] # is unique and different from the server-id on the primary server. Server-id = 7 # primary name or IP address master-host-host = master-server-hostname # if the primary server does not listen on the default port, then you need to determine the master-port option master-port = 3306 # Step 2.1 User name and password created Master-user = username master-password = password # copy operation To target the database (optional, default is all) replicate-do-db = netbar # If the main secondary server connection often fail, it is necessary to increase Master-Retry-Count and Master-Connect-Retry # master-retroyal-count Connection Retry The number of times # master-connection-retroyal After the connection fails, the number of seconds master-retrY-count = 999 master-connection-retry = 601.6 restart After the mysql service is restarted on the sub-server sub-server, you also create a master.info file in the data directory, which contains all information about the replication process (related information about the main server and related information to the primary server). After the initial startup, the sub-server will check this master.info file to get relevant information. If you want to modify the copy option, remove Master.info and restart the MySQL service, use the new option in the Options Profile in the startup process to recreate the master.info file.
Import a bachelor's scrip file (Backup.sql) on the primary server into the sub-server database to ensure the starting point of the copy operation on the primary-sub server.
2 Management Copy Procedure 2.1 Managing primary servers
MySQL> Show ProcessList Displays information about threads on the database server
Mysql> show binlog events Displays information in the binary log (in the version after mysql 4.0)
Mysql> Show Master Status Displays additional information about binary logs in the primary server.
2.2 Managing sub-servers
MySQL> Start Slave is used to start or restore copying, and the start slave command does not mean that everything is OK, you need to use Show Slave Status to oversee the active activities
After the command output of the show slave status, it should contain the value of slave_io_running corresponding to YES, and slave_sql_running corresponds to YES.
MySQL> Stop Slave stops or ends replication
MySQL> Show Slave Status View Server Status Information
Mysql> Change Master to master_def [, master_def] ...
Master_def:
Master_host = 'host_name'
| Master_user = 'user_name'
| Master_password = 'password'
| Master_port = port_num
| Master_connect_retry = count
| Master_log_file = 'master_log_name'
| Master_log_pos = master_log_pos
| Relay_log_file = 'relay_log_name'
| Relay_log_pos = relay_log_pos
| Master_ssl = {0 | 1} | Master_ssl_ca = 'ca_file_name'
| Master_ssl_capath = 'ca_directory_name'
| Master_ssl_cert = 'CERT_FILE_NAME'
| Master_ssl_key = 'key_file_name'
| Master_ssl_cipher = 'cipher_list'
The Change Master To update the parameters of the secondary server and the primary server connection and communication, that is, updated the contents of the master.info and relay-log.info. If you don't need to change the already parameters, you don't need to specify a relative parameter name. The STOP SLAVE will stop the IO_THREAD and SQL_THREAD processes on the secondary server before changing the parameter value.
2.3 Data Recovery
MySQL> Load Data from Master This statement is used in MySQL 4.0 or later, which is a method of obtaining data from the primary server from the primary server. But use this command some restrictions, the current MySQL recommendation can only be used in the case where the primary server database is small and the primary server sets the lock in the case where there is no problem. In addition, only Myisam type tables can use this command to copy data.
4 Appendix 5.1 Options Configuration Files Windows MySQL will find the startup option in the Windows installation directory (such as C: / Winnt or C: / Windows) named my.cnf. Instead, my.cnf can use the file name My.ini, located in the Windows installation directory. UNIX / Linux MySQL will check the /etc/my.cnf file if there is no mysql to use the default startup option. You can also create this file manually. 5.2 Data Directory Windows MySQL installation directory, such as a c: / mysql / data directory UNIX / Linux If it is a source mode installation (tar.gz) mysql, data directory is in / usr / loca / mysql / data directory; if It is installed with RPM packages, data catalogs in / var / lib / mysql directory 5.3 from the mysql database and table in the table
Shell> mysqldump [options] Database [TABLES] If you do not give any table, the entire database will be poured. By performing mysqldump --help, you can get the option table for your MySQLDUMP's version support. Note that if you run mysqldump without --quick or --opt option, MySQLDUMP will load the entire result set to memory before the resulting result, if you are pouring a big database, this may be a problem. MySQLDUMP supports the following options (commonly used): --Add-Locks increases Lock Tables before each table is poured and then UNLOCK TABLE. (In order to make it faster into mysql). -F, --flush-logs is washed away in the Mysql server before starting dumping. -f, --force, even if we get a SQL error during a surface, continue. -h, --host = .. Dipped data from the MySQL server on the named host. The default host is Localhost. -l, --lock-tables. To start dumping all tables. -T, --NO-CREATE-INFO does not write table creation information (CREATE TABLE statement) -d, - NO-DATA does not write any of the line information. If you just want to get the dump of a table, it is useful! --opt with --quick -dd-drop-table --add-locks --extended-insert --lock-tables. You should give you as fast as possible to read a MySQL server. -pyour_pass, --password [= Your_pass] The password used when connecting to the server. If you don't specify a "= Your_Pass" section, MySQLDUMP needs a password from the terminal. -P port_num, --port = port_num The TCP / IP port number used when connecting to a host connection. (This is used to connect to hosts other than localhost because it uses UNIX sockets.) -U user_name, --user = user_name When connecting to the server, MySQL is used by the username. The default is ROOT. The most common mysqldump uses a backup of the entire database: mysqldump --opt database> backup-file.sql
