MySQL Master / Slave Cluster

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.  
分类:Database 时间:2010-05-27 人气:351
blog comments powered by Disqus


  • Get back in the vps host mysql server root password 2010-03-16

    Overview: Recently, a vps customers configure LAMP in time, modify the mysql server root password, a few days forgot your password, ask me how to get it back, Now I will elaborate in detail how to recover root password, at the two case, a mysql datab

  • MYSQL installation solution to enter the current root password method 2011-03-02

    MYSQL, it was found in the installation to enter the current root password Do not remember previously had installed in the computer (your system has been installed MYSQL in the heavy equipment will be asked to enter the original password set, if it i

  • [Reserved] setting. Modification. Mysql root password reset 2010-03-16

    Set, modify, reset mysql root password Overview: This article describes how to set up, modify, reset (if you do not care forget root password) mysql root password, and often I use the mysqladmin command, often appear similar to the following informat

  • Installation Guide for Oracle WebLogic Server 11g Release 1 (10.3.3) 2010-12-04

    Welcome Welcome The Welcome screen is displayed each time you start the installer. Every time you start the installation, this welcome screen will be displayed on the screen. Choose Middleware Home Directory Middleware Home directory selection Specif

  • Turn: F5 help eBay database server load balancing 2008-10-02

    U.S. eBay is the world's largest online trading platform. According to statistics, every day thousands of categories involving several millions of items on eBay to sell; eBay's annual growth rate of 50%. But, compared to the fast-growing business, eB

  • linux system mysql database root password to modify the method 2011-05-12

    The first method: / Local / server / mysql / bin / mysqladmin-u root password 'new_password' When the mysql installation, the default is no root password, use this method to set the installation root password. [Root @ uc ~] # / local / server / mysql

  • ORACLE 9I (Enterprise Edition) Database server installation and configuration 2011-04-28

    ORACLE 9I (Enterprise Edition) Database server installation and configuration Note: Before installing make sure your computer is installed oracle, if not then the following instructions to install, and if installed the first reference oracle oracle u

  • linux system to modify the mysql database root password method 2011-05-12

    The first method: / Local / server / mysql / bin / mysqladmin-u root password 'new_password' As the mysql installation time, there is no default root password, use this installation method to set the root password. [Root @ uc ~] # / local / server /

  • windows to forget root password mysql super-administrator solution 2009-12-25

    Reprinted from: Today, to help a friend retrieve a MYSQL ROOT super administrator password, and start typing the command when the little punched a "point" of harm to toss a few hours, and finally

  • super administrator under windows mysql root password forgotten solution 2010-03-09

    Reprinted from: Today to help a friend retrieve a MYSQL ROOT super administrator password, enter the command when starting a small punched a "point" harm's tossing a few hours, finally resolved, w

iOS 开发

Android 开发

Python 开发



PHP 开发

Ruby 开发






Javascript 开发

.NET 开发



Copyright (C), All Rights Reserved. 版权所有 闽ICP备15018612号

processed in 0.147 (s). 10 q(s)