MySQL data synchronization [active/standby] _ MySQL

MySQL data synchronization [active/standby hot standby] bitsCN.com

Directory

• Application Environment
• Database synchronization mode
• Create a database table
• Configure the database my. ini file
  • Simple interpretation of my. ini file
  • Configure bidirectional synchronization
  • Definition of configuration items
• Set the host and start the slave server thread
  • Show slave status
• Test synchronization
• Solve the problem of auto-growth columns
• Simulation test self-growth column solution
• Troubleshooting
  • Compatibility between MySQL versions
  • ERROR 1201 (HY000)

Application Environment

Database Server

Virtual machine

OS: Windows Server 2003

1. database server 242

IP: 192.168.206.242

2. database server 243

IP: 192.168.206.243

MySQL version

Version: 5.5.2

Query statement:Select version ();

Database synchronization mode

The two servers are mutually master-slave and two-way data synchronization

　　

Create a database table

In order to test bidirectional synchronization, a statement for creating a database and a user table is compiled.

The statement is executed in MySQL on server 242 and server 243 respectively.

CREATE DATABASE wilsondb;USE wilsondb;DROP TABLE IF EXISTS `userinfo`;CREATE TABLE `userinfo` (  `UserId` int(11) NOT NULL AUTO_INCREMENT,  `UserName` varchar(64) DEFAULT NULL,  PRIMARY KEY (`UserId`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Configure the database my. ini file

Find the my. ini file in the installation directory of MySQL 242 and 243 servers

1. simple interpretation of configuration items in the my. ini file

My. ini

# MySQL Server Instance Configuration File # created # Generated by the MySQL Server Instance Configuration Wizard ### Installation Instructions # created # On Linux you can copy this file to/etc/my. cnf to set global options, # mysql-data-dir/my. cnf to set server-specif Ic options # (for this installation) or #~ /. My. cnf to set user-specific options. # On Windows you shoshould keep this file in the installation directory # of your server (e.g. c:/Program Files/MySQL Server X.Y ). to # make sure the server reads the config file use the startup option # "-- defaults-file ". # To run the server from the command line, execute this in a # command line shell, e.g. # mysqld -- defaults-file = "C:/Program Files/MySQL Server X.Y/my. ini "# To install the server as a Windows service manually, execute this in a # command line shell, e.g. # mysqld -- install MySQLXY -- defaults-file = "C:/Program Files/MySQL Server X.Y/my. ini "## And then execute this in a command line shell to start the server, e.g. # net start MySQLXY ### Guildlines for editing this file # plugin # In this file, you can use all long options that the program supports. # If you want to know the options a program supports, start the program # with the "-- help" option. # More detailed information about the inpidual options can also be # found in the manual. ### client section # ---------------------------------------------------------------------- # The following options will be read by MySQL client applications. # Note that only client applications are shipped by MySQL are guaranteed # to read this section. if you want your own MySQL client program to # honor these values, you need to specify it as an option during the # MySQL client library initialization. # client [client] # port = 3306 [mysql] # character set default-character-set = utf8 # server section # character # The following options will be read by the MySQL Server. make sure that # you have installed the server correctly (see above) so it reads this # file. # [mysqld] # The TCP/IP Port the MySQL Server will listen on # MySQL port number Port = 3306 # Path to installation directory. all paths are usually resolved relative to this. # installation Path basedir = "C:/Program Files/MySQL Server 5.5/" # Path to the database root # database root Path datadir = "C: /Documents and Settings/All Users/Application Data/MySQL Server 5.5/Data/"# The default character set that will be used when a new schema or table is # created and no character set is defined # character set character-Set-server = utf8 # The default storage engine that will be used when create new tables when # default storage engine default-storage-engine = INNODB # set the SQL mode to strict # set the strict SQL model # Parameter details: http://www.cnblogs.com/ainiaa/archive/2010/12/31/1923002.html SQL-mode = "STRICT_TRANS_TABLES, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION" # The maximum amount of concurrent sessions the MySQL server will # allow. one of these connections will be reserved for a user with # SUPER privileges to allow the administrator to login even if the # connection limit has been reached. # maximum concurrency on the server, one is to log on to the database max_connections = 100 # Query cache is used to cache SELECT results and later return them # without actual executing the same query once again. having the query # cache enabled may result in significant speed improvements, if your # have a lot of identical queries and rarely changing tables. see the # "Qcache_lowmem_prunes" status variable to check if the current value # is high enough for your load. # Note: In case your tables change very often or if your queries are # textually different every time, the query cache may result in a # slowdown instead of a performance improvement. # Query cache size. Note: when The query is always changed and The table structure is always changed, it is not good to use The cache. query_cache_size = 0 # The number of open tables for all threads. increasing this value # increases the number of file descriptors that mysqld requires. # Therefore you have to make sure to set the amount of open files # allowed to at least 4096 in the variable "open-files-limit" in # section [mysqld_safe] # Table cache number of table_cache = 256 # Maximum size for internal (in-memory) temporary tables. if a table # grows larger than this value, it is automatically converted to disk # based table This limitation is for a single table. there can be used # of them. # the size limit of a single temporary table is tmp_table_size = 26 M # How many threads we shoshould keep in a cache for reuse. when a client # disconnects, the client's threads are put in the cache if there aren't # more than thread_cache_size threads from before. this greatly reduces # the amount of thread creations needed if you have a lot of new # connections. (Normally this doesn't give a notable performance # improvement if you have a good thread implementation .) # How many threads are cached to The client using thread_cache_size = 8 # *** MyISAM Specific options # the maximum size of the temporary file MySQL is allowed to use while # recreating The index (during REPAIR, alter table or load data infile. # If the file-size wocould be bigger than this, the index will be created # through the key cache (which is slower ). # maximum value allowed by temporary files during index reconstruction myisam_max_sort_file_size = 100G # If the temporary file used for fast index creation wocould be bigger # than using the key cache by the amount specified here, then prefer the # key cache method. this is mainly used to force long character keys in # large tables to use the slower key cache method to create the index. # Buffer Size required for table change and re-sorting myisam_sort_buffer_size = 52 M # Size of the Key Buffer, used to cache index blocks for MyISAM tables. # Do not set it larger than 30% of your available memory, as some memory # is also required by the OS to cache rows. even if you're not using # MyISAM tables, you shoshould still set it to 8-64 M as it will also be # used for internal temporary disk tables. # MyISAM table index cache Size. we recommend that you do not exceed 30% key_buffer_size = 8 M # Size of the buffer used for doing full table scans of MyISAM tables. # Allocated per thread, if a full scan is needed. # buffer size during full table scan of MyISAM table (sequential read) read_buffer_size = 64 K # Random read buffer size read_rnd_buffer_size = 256 K # This buffer is allocated when MySQL needs to rebuild the index in # REPAIR, OPTIMZE, ALTER table statements as well as in load data infile # into an empty table. it is allocated per thread so be careful with # large settings. # The buffer size used for sorting sort_buffer_size = 256 K # *** INNODB Specific options *** # Use this option if you have a MySQL server with InnoDB support enabled # but you do not plan to use it. this will save memory and disk space # and speed up some things. # skip-innodb # Additional memory pool that is used by InnoDB to store metadata # information. if InnoDB requires more memory for this purpose it will # start to allocate it from the OS. as this is fast enough on most # recent operating systems, you normally do not need to change this # value. show innodb status will display the current amount used. # set InnoDB storage engine to store data dictionary information and internal data structure memory size innodb_additional_mem_pool_size = 4 M # If set to 1, InnoDB will flush (fsync) the transaction logs to the # disk at each commit, which offers full ACID behavior. if you are # willing to compromise this safety, and you are running small # transactions, you may set this to 0 or 2 to reduce disk I/O to the # logs. value 0 means that the log is only written to the log file and # the log file flushed to disk approximately once per second. value 2 # means the log is written to the log file at each commit, but the log # file is only flushed to disk approximately once per second. # N (N is The value set later) the command for The transaction commit or out of the transaction needs to write The log to the hard disk innodb_flush_log_at_trx_commit = 1 # The size of the buffer InnoDB uses for buffering log data. as soon as # it is full, InnoDB will have to flush it to disk. as it is flushed # once per second anyway, it does not make sense to have it very large # (even with long transactions ). # set innodb_log_buffer_size = 1520 K # InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and # row data for transaction logs of the InnoDB storage engine. the bigger you set this the less disk I/O is needed to # access data in tables. on a dedicated database server you may set this # parameter up to 80% of the machine physical memory size. do not set it # too large, though, because competition of the physical memory may # cause paging in the operating system. note that on 32bit systems you # might be limited to 2-3.5G of user level memory per process, so do not # set it too high. # set the maximum buffer Size for InnoDB to store indexes and table data. innodb_buffer_pool_size = 147 M # Size of each log file in a log group. you shoshould set the combined size # of log files to about 25%-100% of your buffer pool size to avoid # unneeded buffer pool flush activity on log file overwrite. however, # note that a larger logfile size will increase the time needed for the # recovery process. # InnoDB event log size innodb_log_file_size = 74 M # Number of threads allowed inside the InnoDB kernel. the optimal value # depends highly on the application, hardware as well as the OS # scheduler properties. A too high value may lead to thread thrashing. # InnoDB maximum number of concurrent threads innodb_thread_concurrency = 8

PS: In the above code folding experiment, you must manually click the page in the chrome browser to expand it.

2. configure bidirectional synchronization

242 server configuration:

Add the following key/value pairs to the Section [mysqld:

#---------------------------------------------

Servers-id = 242

Log-bin = mysql-bin

Relay-log = relay-bin

Relay-log-index = relay-bin-index

Replicate-do-db = wilsondb

#---------------------------------------------

243 server configuration:

Add the following key/value pairs to the Section [mysqld:

#---------------------------------------------

Servers-id = 243

Log-bin = mysql-bin

Relay-log = relay-bin

Relay-log-index = relay-bin-index

Replicate-do-db = wilsondb

#---------------------------------------------

3. definition of configuration items [copy startup options]

① Server-id: id, unique, value range: 1 to 2 ^ 23-1

② Log-bin: indicates whether to write binary logs when the database changes.

Check whether logs are enabled:

Show variables like '% bin ';

③ Relay-log: this does not explain much. The IO process from the Master binary log to the Slave will write the last line of relay-log. the Slave SQL process will always monitor whether the relay-log has changed, resolution is executed if there is any change.

④ Replicate-to-db: synchronized database

⑤ Other replication startup options: http://dev.mysql.com/doc/refman/5.1/zh/replication.html#replication-options

After the MySQL service is configured on server 242 and server 243, restart the MySQL service separately. if the restart fails, the configuration is incorrect!

Set the host and start the slave server thread

In service 242, run the following statement using the MySQL command line tool:

Mysql> stop slave;

Mysql> change master to master_host = '192. 168.206.243 ', master_user = 'root', master_password = 'abc ';

Mysql> start slave;

Mysql> show slave status/G;

# The maximum length of master_host is 60.

# The maximum length of master_user is 16.

# The maximum length of master_password is 32.

Use MySQL command line tool on service 243 to execute the following statement:

Mysql> stop slave;

Mysql> change master tomaster_host = '192. 168.206.242 ', master_user = 'root', master_password = 'abc ';

Mysql> start slave;

Mysql> show slave status/G;

Show Slave Status

① Slave_IO_Status: the thread has been connected to the master server and is waiting for the arrival of the binary log event. If the master server is idle, it will take a long time. Timeout occurs if the waiting duration is slave_read_timeout seconds. In this case, the thread considers the connection to be interrupted and attempts to reconnect.

More Slave_IO_Status: http://dev.mysql.com/doc/refman/5.1/zh/replication.html#slave-io-thread-states

② Master_Host: host IP address

③ The Master_Log_File and Master_LOG_POS options execute change master to tell the slave server TO re-read binary logs from this point

④ Slave_IO_Running: whether to copy binary data from the Master

⑤ Slave_ SQL _Running: whether to execute the binary data copied from the Master.

⑥ Synchronization is enabled when values of Slave_IO_Running and Slave_ SQL _Running are both Yes;

7. Last_Errno and Last_Error are error messages (if any)

⑧ Seconds_Behind_Master: the number of seconds after the Slave data falls behind the Master, and the synchronization is completed as 0.

Test synchronization

To facilitate the test, a Navicat for MySQL is installed;

Two-way addition, deletion, and modification operations were performed on 242 and 243 databases;

Result: the two-way synchronization is correct.

Solve the problem of auto-growth columns

Cause: When the synchronization is disconnected and new data enters the two servers respectively, the primary key ID is a self-increasing column that will conflict with each other and cause the synchronization to fail.

Find the my. ini file in the installation directory of MySQL 242 and 243 servers

Add the following configuration items at the end of the file:

242 server:

#-------------------------------------

Auto_increment_offset = 1

Auto_increment_increment = 2

#-------------------------------------

243 server:

#-------------------------------------

Auto_increment_offset = 2

Auto_increment_increment = 2

#-------------------------------------

View the modified property value:

Show variables like 'auto _ inc % ';

242 server:

243 server:

AdjustAuto_increment_offsetAndAuto_increment_incrementSo that the self-growth values of the two servers are not repeated, so that the problem of auto-growth of multiple servers is solved;

(To see more obvious auto-increment, I re-generated the table and made the configuration again. In fact, the table structure does not need to be regenerated !)

Simulation test self-growth column solution

During the test, the synchronization is simulated. after the synchronization is disconnected, both databases have data insertion, and the synchronization is enabled again to check whether there is a conflict of auto-increment IDs.

1. stop the Slave thread on server 242 and server 243 respectively.

2. Insert data to the database where server 242 and server 243 are respectively.

Obviously, the auto-incrementing column will not be repeated during insertion;

3. enable server 242 and server 243 Slave threads respectively.

4. view the synchronized data

-------------------------------------------------------------

The Sync was successful and no conflict occurred!

Troubleshooting

Several major problems encountered during the test:

1. replication compatibility between MySQL versions

It is officially recommended to use the latest version. the replication function is continuously improved, and the master/slave servers are recommended to use the same version;

To avoid compatibility issues, MySQL on the two database servers is installed with the same version;

Replication compatibility between different MySQL versions: http://dev.mysql.com/doc/refman/5.1/zh/replication.html#replication-compatibility

2. ERROR 1201 (HY000): cocould not initialize master info structure; more error messages can be found in the MySQL error log

Error 1201 (HY000) appears at start slave;. When an error occurs, use show slave status/G to check the error prompt (Last_IO_Error and Last_ SQL _Error ). Some conflicting SQL operations were performed in the background without the synchronization being turned off as prompted,

Solution: ① stop the SQL service

② Delete master.info and relay-log.info files

③ Change master to master_host = 'xxxxxx ',

Master_user = 'xxxx', master_password = 'xxxx ';

④ Restart the SQL Server and then start slave;

In the process, COSCO not only encountered these errors, but did not record the details. they all looked at the relevant prompts to correct them. they all encountered operation problems or were careful;

BitsCN.com