MySQL Master / Slave Cluster

sponsored links
MySQL Master / Slave Cluster


Chen Heng

1 Installation

1. Download mysql-5.1.40 source

2. Compile

# Prepared

Shell>. / Configure - prefix = / vogins / lib / mysql - with-charset = utf8 - with-mysqld-user = mysql - with-plugins = max

- Prefix: the main installation directory

- With-charset: database default encoding

- With-mysqld-user: specifies the user running mysqld daemon

- With-plugins: data engine to support plug-ins

# Compile

Shell> make

# Installation

Shell> make install

3. In the other servers step 2

2 preparation

1. Initialized database

# Go to the home directory mysql

Shell> cd / vorgins / lib / mysql

# Initialize database

Shell> bin / mysql_install_db - user = mysql

2. The other servers step 1

3. My.cnf file edit Master Database

# Copy my-large.cnf to / etc / my.cnf. Preparation has presided over the default Master / Slave replication.

cp share / mysql / my-large.cnf / etc / my.cnf

4. Start Master

# Use mysql to run mysqd daemon

bin / mysqld_safe-user = mysql &

5. Modify the Master database root password:

Shell> mysqladmin-uroot-p password 'lark @ vogins'

6. Landing Master Database

Shell> mysql-uroot-plark @ vogins

7. Set replication account (the account must grant REPLICATION SLAVE privileges)

Mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.' IDENTIFIED BY 'repl @ vogins';

8. For the Master to do snapshots (if it is newly installed database, this step can not do)

# Statement, all tables and block write clear statements, the InnoDB engine, has returned to lock the COMMIT operation


# Use the tar tool to create data back-up (Do not drive window, do not close the mysql client window to keep the table # # global read lock)

Shell> tar-cvf / tmp / master-snapshot.tar. / Var /

# This file reached the Slave database server / tmp, the extracted files, and the need for a database catalog text # # # items copied to the Slave's database home directory / var / directory.

Shell> tar-xvf / tmp / master-snapshot.tar

Shell> cp your-db $ MYSQL / var /

# Back to the Master database, the abolition of the global read lock


# For InnoDB, the best table in the lock was closed before making a snapshot of Master. Basically the same way, only the data lock # # after the close, restart the database after the snapshot.

Shell> bin / mysqladmin-uroot-plark @ vogins shutdown

9. Setting Slave my.cnf file database

# Copy my-large.cnf to / etc / my.cnf.

cp share / mysql / my-large.cnf / etc / my.cnf

# Adjust the Slave configuration file parameters

# Comment out the binary log, from the server does not need

Vim> # log-bin = mysql-bin

Vim> # binlog_format = mixed

# Modify the server-id, and can not be the same with the Master and other Slave

Vim> server-id = 2

Vim> master-host =

Vim> master-user = repl

Vim> master-password = repl @ vogins

# Rule does not require synchronization of the database

Vim> replicate-ignore-db = mysql, test

# Other preparation no adjustment.

10. Start Slave

# Use mysql to run mysqd daemon

# Please note that to ensure that the Master to allow Slave to access the 3306 port.

bin / mysqld_safe-user = mysql &

11. Modify the Master database root password:

Shell> mysqladmin-uroot-p password 'loon @ vogins'

Of 12. Landing Slave database

Shell> mysql-uroot-ploon @ vogins

13. See Slave running

Mysql> show slave status

3 Management

3.1 Management Master Server



Used to remove out in the specified log or date log of all the binary log index. These logs will be recorded in the log from the list of index file is deleted, so that the log was given as the first.

For example:

PURGE MASTER LOGS TO 'mysql-bin.010';
PURGE MASTER LOGS BEFORE '2003-04-02 22:46:26';

BEFORE date variable argument for 'YYYY-MM-DD hh: mm: ss' format. MASTER and BINARY are synonyms.

If you have an active slave server, the server currently is reading the log you are trying to delete one, the statement does not work, but will fail with an error. However, if a slave is dormant and you happen to clean up its one of the logs you want to read, then the slave server starts, can not replicate. When the slave server is copied, this statement is safe to run. You do not need to stop them.

To purge logs, follow these steps:

1. On each slave server, use SHOW SLAVE STATUS to check which log it is reading.

2. Use of SHOW MASTER LOGS get a log on the master server.

3. In all the slave servers determine the earliest log. This is the target log. If all the slaves are up to date, this is the last log on the list.

4. Make you want to delete all the log backup. (This step is optional, but recommended.)

5. Clean up all the logs, but not including the target log.


You can delete the file listed in the index of all the binary log, the binary log index file to re-set is empty, and create a new binary log file.



If the client uses a connection SUPER privileges account, you can disable or enable binary logging is currently connected. If the client does not have this permission, the statement is rejected, and is accompanied by an error.


   [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

Used to display the binary log event. If you do not specify 'log_name', the first binary log is displayed.



For the list server binary log file.



For binary log files provide the primary server state information.



Use the primary server is used to display the current registration list of the replication slave server. Not to - report-host = slave_name option at the beginning of the slave server does not appear in this list.

3.2 Slave Server Management


CHANGE MASTER TO master_def [, master_def] ... 
      MASTER_HOST = 'host_name'
    | MASTER_USER = 'user_name'
    | MASTER_PASSWORD = 'password'
    | MASTER_PORT = port_num
    | 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'

You can change the slave server and master server for the connection and communication parameters.

MASTER_USER, MASTER_PASSWORD, MASTER_SSL, MASTER_SSL_CA, MASTER_SSL_CAPATH, MASTER_SSL_CERT, MASTER_SSL_KEY and MASTER_SSL_CIPHER for information on how to slave and master server connection information.



This command is used on the master server snapshot, and copied to the slave server. It can be updated and MASTER_LOG_POS MASTER_LOG_FILE value, so that slave servers can be started from the correct location to copy. Use - replicate-*- do-* and - replicate-*- ignore-* options specified in the table and database exclusion rules were honored. - Replicate-rewrite-db is not taken into consideration. This is because the use of this option, users can set up an example - replicate-rewrite-db = db1-> db3 and - replicate-rewrite-db = db2-> db3 non-unique mapping. When loading tables from the master, the slave server will map to confusion.

The use of this statement subject to the following conditions:

* Only works on MyISAM tables. If you try to load a non-MyISAM table would cause the following error:

 ·                  ERROR 1189 (08S01): Net error reading from master

When the snapshot will be given to the master server's global read lock. During the loading operation, the lock will prevent an update on the master server.

If you are loading large tables, you may have on the master server and slave servers to increase net_read_timeout and net_write_timeout value.

LOAD DATA FROM MASTER does not copy any tables from the mysql database. This can more easily get the main server and slave server with different users and permissions.

LOAD DATA FROM MASTER statement is required for connecting the main server, copy the account to get the main server has a RELOAD and SUPER privileges, and all you want to load master table has SELECT permission. All the user does not have SELECT permissions on the primary server table were ignored LOAD DATA FROM MASTER. This is because the main server user will hide them: LOAD DATA FROM MASTER will call the SHOW DATABASES to know the main server database to load, but SHOW DATABASES returns only some of the user rights database. In the slave server, the publishing user LOAD DATA FROM MASTER should have authority to cancel or create databases and tables are copied.



Used to copy a table from the master server to slave servers. The main role of this statement debugging LOAD DATA FROM MASTER. It requires the account used to connect the main server has the master server RELOAD and SUPER privileges, and has the main server to load the table SELECT permission. In the slave server, the release LOAD TABLE FROM MASTER should have canceled and the user permissions to create tables.

For LOAD DATA FROM MASTER conditions also apply here. Example, LOAD TABLE FROM MASTER works only for MyISAM tables. On the LOAD DATA FROM MASTER note also applies to the suspension.


        SELECT MASTER_POS_WAIT('master_log_file', master_log_pos)

This is actually a function, not a statement. It is used to confirm that slave has read and performed to the main server binary log to the given position.



To forget the slave server for the main server in the replication of binary log position. This statement is used for a clear start: it will delete and files, and all the relay logs and start a new relay log.

Note: All relay logs are deleted, even if they are not slave SQL thread is completely implemented. (If you have already released a SLAVE statement or if a slave's load is large enough, then for a replication slave server, this is a very possible situation.) files stored in the connection information by using the startup options in the corresponding specified value, is immediately reset the. This information includes the primary server host, master server interface, the main server user and master password equivalents. When the slave SQL thread is suspended, it is copied in the temporary table in the middle, and released the RESET SLAVE, already copied the temporary table is deleted in the slave server.



Skip the main server from the n-back events. To recover from the statement caused replication termination, it is useful.

Not only when the slave thread is running, this statement effective. Otherwise, will produce an error.



Used to provide the key to the slave thread parameters. If you use the mysql client issued this statement, you can use a \ G statement terminator to be more easy to read vertical layout instead of a semicolon:


START SLAVE [thread_type [, thread_type] ... ]
    MASTER_LOG_FILE = 'log_name', MASTER_LOG_POS = log_pos
    RELAY_LOG_FILE = 'log_name', RELAY_LOG_POS = log_pos
thread_type: IO_THREAD | SQL_THREAD

START SLAVE without options will start the two slave threads simultaneously. I / O thread reads queries from the master server and stores them in the relay log. SQL thread reads the relay logs and to execute the query. SUPER START SLAVE required permissions.

If START SLAVE successfully start the slave threads, it will return no error. But even in this case, there may be such a phenomenon - the server starts a thread, and then it stops (for example, because they do not successfully connect to the master server, or do not can read the binary logs, or there other issues). START SLAVE does not warn this. You must check the slave server's error log, see if the thread generated by the slave server error messages, or use the SHOW SLAVE STATUS check whether they are functioning properly.

You can IO_THREAD and SQL_THREAD option added to the statement, specifying which thread will be started.

You can add an UNTIL clause, specify the slave server should be up and running until the SQL thread to the main server binary logs up to a given point. When the SQL thread to reach this point, it will stop. If the statement specified SQL_THREAD option, then it will start the SQL thread. Otherwise, it will also launch two slave threads. If the SQL thread is running, the UNTIL clause is ignored, and issue a warning.

For an UNTIL clause, you must also specify a log file name and location. Not to the main server and relay log options combined.

UNTIL condition by a subsequent STOP SLAVE statement or an UNTIL clause of START SLAVE, including statements, or a server restart command to re-set.

UNTIL clause is useful for debugging replication, or can be used to facilitate copying operation continues until a certain point close to ending, at this point, you want to avoid a statement to slave server replication. For example, if the main service on the implementation of an unwise DROP TABLE statement, you can use UNTIL to tell slave server, the implementation of this point to stop, not to continue. To find what the event will take on the primary server logs or slave relay logs using mysqlbinlog, or by using the SHOW BINLOG EVENTS statement.

If you are using UNTIL, so that slave servers handle section has been copied into the query, it is recommended that you use - skip-slave-start option to start the slave server to prevent when the slave server starts, SQL thread is running. The best option in a file using this option, but not in the command line, so that the unexpected event of server restart, it will not be forgotten.

SHOW SLAVE STATUS statement includes output fields. These fields show the current value UNTIL condition.

In previous versions of MySQL, this statement is referred to as SLAVE START. Still accepted in MySQL 5.1 this use to work with previous versions. But now deprecated.


TOP SLAVE [thread_type [, thread_type] ... ]
 Support, please click here.  
  • StumbleUpon
  • Digg
  • TwitThis
  • Mixx
  • Technorati
  • Facebook
  • NewsVine
  • Reddit
  • Google
  • LinkedIn
  • YahooMyWeb

Related Posts of MySQL Master / Slave Cluster

  • Eclipse to run using the specified JVM m2eclipse plugin can not find tools.jar

    Used the m2eclipse plug-ins using struts2 when com.sun necessary to rely on the default-tools.jar, specifically because at the struts-annotations bag designated default-tools.jar <profile> <id> default-tools.jar </ id> <activatio ...

  • autotest, make your test automation RSpec (b) [windows]

    Following Part <<autotest, Let your rspec test automation >> We are the RSpec implementation of automated testing, but always want to open a dos shell look at the recent test results are also too cumbersome. Well, let us to achieve automated t

  • Struts + Spring + Hibernate practice

    Tools: Eclipse3.1, MyEclipse4.03, Tomcat5.5.9, Properties Editor plug-ins, MySql4.1.13 New construction: the name for the login Create Struts framework Create index.jsp, add a link to login.jsp Press Ctrl + N, to create login.jsp, LoginAction, the use of

  • About rails install mysql driver error problem

    My local wamp server using one of the key package, only used mysql functions, today in the use of rake db: migrate when First reported a month can not be implemented, the next inquiry and found no mysql installed a gem, to install gem install mysql i ...

  • In the Linux platform to install and configure Ruby on Rails Detailed

    ruby on rails recommend the production environment is running Linux / FreeBSD / Unix, or Unix family of operating systems, using lighttpd + FCGI solution. The following will be my Linux operating system, lighttpd + FCGI, MySQL database as an example, comp

  • Hibernate easy extract

    Simple terms the following excerpt from hibernate: DAO mode, the database access layer implementation is hidden to the Data Accessor, the front said, DAO mode are in fact a combination of two modes, namely Data Accessor and domain Object mode. What i ...

  • Answer: After 2.2 upgrade mysql question-driven

    windows installed after the mysql gem error, error is approximately: ArgumentError (NULL pointer given): (eval): 3: in `each_hash ' (eval): 3: in `all_hashes' This is feeling under the windows of the question if the mysql gem version libmysql ...

  • jBPM Development Getting Started Guide

    Although the workflow is still immature stage of development, not even a recognized standard. But its application has already been launched in the Express, indicating the market's demand for job-flow framework are urgent and enormous. Backgrounds of o

  • Openfire Plugin Developer's Guide

    Introduction Openfire features plug-ins are enhanced. This document is a guide to developers to create plug-ins. The structure of a plug-in Plug-ins Plug-ins openfireHome stored in the directory. When deploying a plug-in jar or war file, it will automatic

  • What is the appfuse

    First, Appfuse brief introduction Matt Raible are Appfuse developed a guiding entry-level J2EE framework, how to integrate its popular Spring, Hibernate, ibatis, struts, Xdcolet, junit, etc. give the basic framework of the model, the latest version 1.7 is

blog comments powered by Disqus
Recent Entries
Tag Cloud
Random Entries