Thissection describes how to set up complete replication of a MySQL server. Thereare a number of different methods for setting up replication, and the exact methodto use depends on how you are setting up replication, and whether you alreadyhave data within your master database.
There aresome generic tasks that are common to all replication setups:
• On themaster, you must enable binary logging and configure a unique server ID.This might require a server restart.SeeSection17.1.1.1, “Setting the Replication Master Configuration”.
• On eachslave that you want to connect to the master,you mustconfigure a unique server ID. This might require a server restart.SeeSection 17.1.1.2, “Setting the Replication Slave Configuration”.
•Optionally, create a separate user for your slaves to use during authenticationwith the master when reading the binary log for replication. SeeSection17.1.1.3, “Creating a User for Replication”.
• Beforecreating a data snapshot or starting the replication process, you should recordthe position of the binary log on the master. You will need this informationwhen configuring the slave so that the slave knows where within the binary logto start executing events. See Section 17.1.1.4, “Obtaining theReplication Master Binary Log Coordinates”.
• If youalready have data on your master and want to use it to synchronize your slave,you need to create a data snapshot. There are different methods to create thedatabase snapshot, depending on the size of the database and the location ofthe files. Create a snapshot using mysqldump(see Section 17.1.1.5, “Creating a Data SnapshotUsing mysqldump”) or by copying the data files directly (seeSection17.1.1.6, “Creating a Data Snapshot Using Raw Data Files”).
•Configure the slave with settings for connecting to the master, such as thehost name, login
credentials,and binary log file name and position. SeeSection17.1.1.10, “Setting the Master
Configurationon the Slave”.
17.1.1.1 Setting the Replication Master Configuration
On areplication master, you must enable binary logging and establish a uniqueserver ID. If this has not already been done, a server restart is required.
Binarylogging must be enabled on the master because the binary log is thebasis for replicating changes from the master to its slaves. If binary loggingis not enabled using thelog-binoption,replication is not possible.
Eachserver within a replication group must be configured with a unique server ID.This ID is used to identify individual servers within the group, and must be apositive integer between 1 and (2³²)−1.How you organize and select the numbers is your choice.
Toconfigure the binary log and server ID options, shut down the MySQL server andedit themy.cnformy.inifile. Within the [mysqld] section of the configurationfile, add thelog-binandserver-idoptions. If these options alreadyexist, but are commented out, uncomment the options and alter them according toyour needs. For example, to enable binary logging using a log file name prefixofmysqlbin, andconfigure a server ID of 1, use these lines:
[mysqld]
log-bin=mysql-bin
server-id=1
Aftermaking the changes, restart the server.
Note
If youomit server-id[2193](orset it explicitly to its default value of 0), the master refuses anyconnections from slaves.
Note
For thegreatest possible durability and consistency in a replication setup usingInnoDBwith transactions, you should useinnodb_flush_log_at_trx_commit=1andsync_binlog=1in the master my.cnf file.
Note
Ensurethat the skip-networking optionis not enabled on your replication master. If networking has been disabled, theslave can not communicate with the master and replication fails.
17.1.1.2 Setting the Replication Slave Configuration
On areplication slave, you must establish a unique server ID. If this has notalready been done, this part of slave setup requires a server restart.
If theslave server ID is not already set, or the current value conflicts with thevalue that you have chosen for the master server, shut down the slave serverand edit the[mysqld]sectionof the configuration file to specify a unique server ID. For example:
[mysqld]
server-id=2
Aftermaking the changes, restart the server.
If youare setting up multiple slaves, each one must have a uniqueserver-id[2193]valuethat differs from that of the master and from any of the other slaves.
Note
If youomit server-id[2193](orset it explicitly to its default value of 0), the slave refuses to connect to amaster.
You donot have to enable binary logging on the slave for replication to be set up.However, if you enable binary logging on the slave, you can use the slave'sbinary log for data backups and crash recovery, and also use the slave as partof a more complex replication topology. For example, where this slave then actsas a master to other slaves.
17.1.1.3 Creating a User for Replication
Eachslave connects to the master using a MySQL user name and password, so theremust be a user account on the master that the slave can use to connect. Anyaccount can be used for this operation, providing it has been granted the REPLICATION SLAVE privilege.You can choose to create a different account for each slave, or connect to themaster using the same account for each slave.
Althoughyou do not have to create an account specifically for replication, you shouldbe ware that the replication user name and password are stored in plain text inthe master info repository file or table (seeSection17.2.2.2, “Slave Status Logs”). Therefore, you may want tocreate a separate account that has privileges only for the replication process,to minimize the possibility of compromise to other accounts.
To createa new account, use CREATE USER.To grant this account the privileges required for
replication,use the GRANT statement.If you create an account solely for the purposes of replication, that accountneeds only theREPLICATION SLAVEprivilege.For example, to set up a new user,repl,that can connect for replication from any host within theydomain.comdomain, issue these statementson the master:
mysql> CREATE USER'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass';
mysql> GRANTREPLICATION SLAVE ON *.* TO 'repl'@'%.mydomain.com';
See Section13.7.1, “Account Management Statements”, formore information on statements for manipulation of user accounts.
17.1.1.4 Obtaining the Replication Master Binary LogCoordinates
You needthe master's current coordinates within its binary log in order to configurethe slave to start the replication process at the correct point.
If you haveexisting data on your master that you want to synchronize on your slaves beforestarting the replication process, you must stop processing statements on themaster, and then obtain its current binary log coordinates and dump its data,before permitting the master to continue executing statements.If you do not stop the execution of statements, the data dump and the masterstatus information that you use will not match and you will end up withinconsistent or corrupted databases on the slaves.
To obtainthe master binary log coordinates, follow these steps:
1. Starta session on the master by connecting to it with the command-line client, andflush all tables and block write statements by executing theFLUSH TABLES WITH READ LOCKstatement:
mysql> FLUSH TABLESWITH READ LOCK;
For InnoDB tables, FLUSH TABLES WITH READ LOCK alsoblocksCOMMIToperations.
Warning
Leave theclient from which you issued theFLUSH TABLESstatementrunning so that the read lock remains in effect. If you exit the client, thelock is released.
2. In adifferent session on the master, use theSHOW MASTER STATUSstatement to determine thecurrent binary log file name and position:
mysql > SHOW MASTERSTATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 73 | test | manual,mysql |
+------------------+----------+--------------+------------------+
The File column shows the name of the logfile and Position shows theposition within the file.
In thisexample, the binary log file is mysql-bin.000003and the position is 73. Record these values. You need them laterwhen you are setting up the slave.They represent thereplication coordinates at which the slave should begin processing new updatesfrom the master.
If themaster has been running previously without binary logging enabled, the log filename and position values displayed bySHOWMASTER STATUSormysqldump --master-datawillbe empty. In that case, the values that you need to use later when specifyingthe slave's log file and position are the empty string ('') and4.
You nowhave the information you need to enable the slave to start reading from thebinary log in the correct place to start replication.
If youhave existing data that needs be to synchronized with the slave before youstart replication, leave the client running so that the lock remains in placeand then proceed toSection 17.1.1.5, “Creating a Data SnapshotUsing mysqldump”, orSection17.1.1.6, “Creating a Data Snapshot Using Raw Data Files”.The idea here is to prevent any further changes so that the data copied to theslaves is in synchrony with the master.
If youare setting up a brand new master and slave replication group, you can exit thefirst session to release the read lock.
17.1.1.5 Creating a Data Snapshot Using mysqldump
One wayto create a snapshot of the data in an existing master database is to use themysqldumptool to create a dump of all thedatabases you want to replicate. Once the data dump has been completed, youthen import this data into the slave before starting the replication process.
Theexample shown here dumps all databases to a file nameddbdump.db, andincludesthe--master-dataoptionwhich automatically appends theCHANGE MASTER TOstatementrequired on the slave to start the replication process:
shell> mysqldump--all-databases --master-data > dbdump.db
If you donot use--master-data, then itis necessary to lock all tables in a separate session manually (usingFLUSH TABLES WITH READ LOCK) prior torunningmysqldump, thenexiting or running UNLOCK TABLES from the second session to release the locks.You must also obtain binary log position information matching the snapshot,usingSHOW MASTER STATUS,and use this to issue the appropriateCHANGEMASTER TOstatement when starting the slave.
Whenchoosing databases to include in the dump, remember that you need to filter outdatabases on each slave that you do not want to include in the replicationprocess.
To importthe data, either copy the dump file to the slave, or access the file from themaster when connecting remotely to the slave.
17.1.1.6 Creating a Data Snapshot Using Raw Data Files
If yourdatabase is large, copying the raw data files can be more efficient than usingmysqldumpand
importingthe file on each slave. This technique skips the overhead of updating indexesas theINSERTstatementsare replayed.
Usingthis method with tables in storage engines with complex caching or loggingalgorithms requires extra steps to produce a perfect “point in time” snapshot:the initial copy command might leave out cache information and logging updates,even if you have acquired a global read lock. How the storage engine respondsto this depends on its crash recovery abilities.
Thismethod also does not work reliably if the master and slave have differentvalues for
ft_stopword_file,ft_min_word_len, orft_max_word_lenand you are copying tables havingfull-text indexes.
If youuse InnoDBtables,you can use themysqlbackupcommandfrom the MySQL Enterprise Backup component to produce a consistent snapshot.This command records the log name and offset corresponding to the snapshot tobe later used on the slave. MySQL Enterprise Backup is a commercial productthat is included as part of a MySQL Enterprise subscription. SeeSection25.2, “MySQL Enterprise Backup Overview”fordetailed information.
Otherwise,use the cold backup technique to obtain a reliablebinary snapshot of InnoDB tables:copy all data files after doing a slow shutdownofthe MySQL Server.
To createa raw data snapshot of MyISAM tables,you can use standard copy tools such as cp or copy,a remote copy tool such as scp orrsync, an archiving tool such as zip or tar, or a file system snapshot tool such asdump, providing that your MySQL datafiles exist on a single file system.
If youare replicating only certain databases, copy only those files that relate tothose tables. (ForInnoDB,all tables in all databases are stored in thesystemtablespacefiles, unless you have the innodb_file_per_tableoption enabled.)
You mightwant to specifically exclude the following files from your archive:
• Filesrelating to the mysql database.
• Themaster info repository file, if used (seeSection17.2.2, “Replication Relay and Status Logs”).
• Themaster's binary log files.
• Anyrelay log files.
To getthe most consistent results with a raw data snapshot, shut down the masterserver during the process, as follows:
1.Acquire a read lock and get the master's status. SeeSection17.1.1.4, “Obtaining the Replication
MasterBinary Log Coordinates”.
2. In aseparate session, shut down the master server:
shell> mysqladminshutdown
3. Make acopy of the MySQL data files. The following examples show common ways to dothis. You
need tochoose only one of them:
shell> tar cf/tmp/db.tar./data
shell> zip -r/tmp/db.zip./data
shell> rsync--recursive./data /tmp/dbdata
4.Restart the master server.
If youare not using InnoDB tables,you can get a snapshot of the system from a master without shutting down theserver as described in the following steps:
1.Acquire a read lock and get the master's status. SeeSection17.1.1.4, “Obtaining the Replication Master Binary Log Coordinates”.
2. Make acopy of the MySQL data files. The following examples show common ways to dothis. You need to choose only one of them:
shell> tar cf/tmp/db.tar./data
shell> zip -r/tmp/db.zip./data
shell> rsync--recursive./data /tmp/dbdata
3. In theclient where you acquired the read lock, release the lock:
mysql> UNLOCK TABLES;
Once youhave created the archive or copy of the database, copy the files to each slavebefore starting the slave replication process.
17.1.1.7 Setting Up Replication with New Master and Slaves
The easiest and moststraightforward method for setting up replication is to use new master andslave servers.
You can also use thismethod if you are setting up new servers but have an existing dump of the databasesfrom a different server that you want to load into your replicationconfiguration. By loading the data into a new master, the data will beautomatically replicated to the slaves.
To set up replicationbetween a new master and slave:
1.Configure the MySQL master with the necessary configuration properties. SeeSection17.1.1.1, “Setting the Replication Master Configuration”.
2. Startup the MySQL master.
3. Set upa user. See Section 17.1.1.3, “Creating a User for Replication”.
4. Obtainthe master status information. SeeSection 17.1.1.4, “Obtainingthe Replication Master Binary Log Coordinates”.
5. On themaster, release the read lock:
mysql> UNLOCK TABLES;
6. On theslave, edit the MySQL configuration. SeeSection17.1.1.2, “Setting the Replication Slave Configuration”.
7. Startup the MySQL slave.
8.Execute a CHANGE MASTER TO statementto set the master replication server configuration. SeeSection17.1.1.10, “Setting the Master Configuration on the Slave”.
Performthe slave setup steps on each slave.
Becausethere is no data to load or exchange on a new server configuration you do notneed to copy or import any information.
If youare setting up a new replication environment using the data from a differentexisting database server,you will now need to run the dumpfile generated from that server on the new master. The database updates willautomatically be propagated to the slaves:
shell> mysql -h master< fulldb.dump
17.1.1.8 Setting Up Replication with Existing Data
Whensetting up replication with existing data, you will need to decide how best toget the data from the master to the slave before starting the replicationservice.
The basicprocess for setting up replication with existing data is as follows:
1. Withthe MySQL master running, create a user to be used by the slave when connectingto the master during replication. SeeSection 17.1.1.3, “Creatinga User for Replication”.
2. If youhave not already configured theserver-id[2193]and enabled binary logging on the master server, you will need toshut it down to configure these options. SeeSection17.1.1.1, “Setting the Replication Master Configuration”.
If youhave to shut down your master server, this is a good opportunity to take asnapshot of its databases. You should obtain the master status (seeSection17.1.1.4, “Obtaining the Replication Master Binary Log Coordinates”)before taking down the master, updating the configuration and taking asnapshot. For information on how to create a snapshot using raw data files, seeSection 17.1.1.6, “Creating a Data Snapshot Using Raw Data Files”.
3. Ifyour master server is already correctly configured, obtain its status (seeSection17.1.1.4,
“Obtainingthe Replication Master Binary Log Coordinates”) andthen usemysqldumpto take asnapshot (seeSection 17.1.1.5, “Creating a Data Snapshot Using mysqldump”)or take a raw snapshot of the live server using the guide inSection17.1.1.6, “Creating a Data Snapshot Using Raw Data Files”.
4. Updatethe configuration of the slave. SeeSection 17.1.1.2, “Settingthe Replication Slave
Configuration”.
5. Thenext step depends on how you created the snapshot of data on the master.
If youused mysqldump:
a. Startthe slave, using the --skip-slave-startoption so that replication does not start.
b. Importthe dump file:
shell> mysql <fulldb.dump
If youcreated a snapshot using the raw data files:
a.Extract the data files into your slave data directory. For example:
shell> tar xvfdbdump.tar
You mayneed to set permissions and ownership on the files so that the slave server can
accessand modify them.
b. Startthe slave, using the --skip-slave-startoption so that replication does not start.
6.Configure the slave with the replication coordinates from the master. Thistells the slave the binary log file and position within the file wherereplication needs to start. Also, configure the slave with the logincredentials and host name of the master. For more information on the CHANGE MASTER TO statementrequired, see Section 17.1.1.10, “Setting the Master Configuration on the Slave”.
7. Startthe slave threads:
mysql> START SLAVE;
After youhave performed this procedure, the slave should connect to the master and catchup on any updates that have occurred since the snapshot was taken.
If youhave forgotten to set the server-id [2193]option for the master, slaves cannot connect to it.
If youhave forgotten to set the server-id [2193]option for the slave, you get the following error in the slave'serror log:
Warning: You should set server-id to a non-0 value if master_host isset; we will force server id to 2, but this MySQL server will not act as aslave.
You alsofind error messages in the slave's error log if it is not able to replicate forany other reason.
The slaveuses information stored in its master info repository to keep track of how muchof the master's binary log it has processed. The repository can be in the formof files or a table, as determined by the value set for --master-info-repository. When aslave runs with--master-inforepository=FILE,you can find in its data directory two files, namedmaster.infoandrelaylog.info. If--master-info-repository=TABLEinstead,this information is saved in the tablemaster_slave_infoin themysqldatabase. In either case, donotremove or edit the files or table unless you know exactly what you aredoing and fully understand the implications. Even in that case, it is preferredthat you use theCHANGE MASTER TOstatementto change replication parameters.
The slavecan use the values specified in the statement to update the status filesautomatically. SeeSection 17.2.2, “Replication Relay and StatusLogs”, for more information.
Note
Thecontents of the master info repository override some of the server options specifiedon the command line or inmy.cnf.SeeSection 17.1.4, “Replication and Binary Logging Options andVariables”, for more details.
A single snapshot ofthe master suffices for multiple slaves. To set up additional slaves, use thesame master snapshot and follow the slave portion of the procedure justdescribed.
17.1.1.9 Introducing Additional Slaves to an ExistingReplication Environment
To addanother slave to an existing replication configuration, you can do so withoutstopping the master. Instead, set up the new slave by making a copy of anexisting slave, except that you configure the new slave with a different server-id [2193]value.
Toduplicate an existing slave:
1. Shutdown the existing slave:
shell> mysqladminshutdown
2. Copythe data directory from the existing slave to the new slave. You can do this bycreating an archive usingtarorWinZip, or by performing a direct copyusing a tool such ascporrsync.
Ensurethat you also copy the log files and relay log files.
A commonproblem that is encountered when adding new replication slaves is that the newslave fails with a series of warning and error messages like these:
071118 16:44:10 [Warning] Neither --relay-log nor--relay-log-index were used; so
replication may break when this MySQL server acts as a slave andhas his hostname
changed!! Please use '--relay-log=new_slave_hostname-relay-bin'to avoid this problem.
071118 16:44:10 [ERROR] Failed to open the relay log './old_slave_hostname-relay-bin.003525'
(relay_log_pos 22940879)
071118 16:44:10 [ERROR] Couldnot find target log during relay log initialization
071118 16:44:10 [ERROR] Failedto initialize the master info structure
This isdue to the fact that, if the --relay-log optionis not specified, the relay log files contain the host name as part of theirfile names. (This is also true of the relay log index file if the--relay-log-indexoption isnot used. SeeSection 17.1.4, “Replication and Binary Logging Options andVariables”, for more information about these options.)
To avoidthis problem, use the same value for--relay-logon the new slave that was
used onthe existing slave. (If this option was not set explicitly on the existingslave, use
existing_slave_hostname-relay-bin.) If this is not feasible, copythe existing slave's relay log index file to the new slave and set the--relay-log-indexoption onthe new slave to match what was used on the existing slave. (If this option wasnot set explicitly on the existing slave, useexisting_slave_hostname-relay-bin.index.) Alternatively—if you havealready tried to start the new slave (after following the remaining steps inthis section) and have encountered errors like those described previously—thenperform the following steps:
a. If youhave not already done so, issue aSTOPSLAVEon the new slave.
If youhave already started the existing slave again, issue aSTOP SLAVEon the existing slave as well.
b. Copythe contents of the existing slave's relay log index file into the new slave'srelay log index file, making sure to overwrite any content already in the file.
c.Proceed with the remaining steps in this section.
3. Copythe master info and relay log info repositories (seeSection17.2.2, “Replication Relay and Status Logs”) fromthe existing slave to the new slave. These hold the current log coordinates forthe master's binary log and the slave's relay log.
4. Startthe existing slave.
5. On thenew slave, edit the configuration and give the new slave a uniqueserver-id[2193]not usedby the master or any of the existing slaves.
6. Start the newslave. The slave uses the information in its master info repository to startthe
replication process.
17.1.1.10 Setting the Master Configuration on the Slave
To set upthe slave to communicate with the master for replication, you must tell theslave the
necessaryconnection information. To do this, execute the following statement on theslave, replacing the option values with the actual values relevant to yoursystem:
mysql> CHANGE MASTERTO
-> MASTER_HOST='master_host_name',
-> MASTER_USER='replication_user_name',
-> MASTER_PASSWORD='replication_password',
-> MASTER_LOG_FILE='recorded_log_file_name',
-> MASTER_LOG_POS=recorded_log_position;
Note
Replicationcannot use Unix socket files.You must be able to connect to the masterMySQL server using TCP/IP.
The CHANGE MASTER TOstatementhas other options as well. For example, it is possible to set up securereplication using SSL. For a full list of options, and information about themaximum permissible length for the string-valued options, seeSection13.4.2.1, “CHANGE MASTER TO Syntax”.
Example:
Install redhat 6.4 32bit for VM(192.168.20.111 mysql).
Install MySQL 5.6.27. refer to http://blog.csdn.net/songyongbo_107/article/details/49818641
create table test.t;
mysql> desc t;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | varchar(10) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)Begin:
[root@mysql ~] $ cat /usr/my.cnf
:::
[mysqld]
<span style="color:#ff0000;">log_bin = mysql-bin
server-id = 1</span>
:::
[root@mysql ~] $ [root@slave ~] $ cat /usr/my.cnf
:::
[mysqld]
<span style="color:#ff0000;">server_id = 2</span>
:::
[root@slave ~]mysql> CREATE USER 'repl'@'192.168.20.%' IDENTIFIED BY 'rep1';
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.20.%' IDENTIFIED BY 'repl';
Query OK, 0 rows affected (0.00 sec)
mysql> mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000005 | 500 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)On slave:
mysql> CHANGE MASTER TO
-> MASTER_HOST='192.168.20.111',
-> MASTER_USER='repl',
-> MASTER_PASSWORD='repl',
-> MASTER_LOG_FILE='mysql-bin.000005',
-> MASTER_LOG_POS=500;
Query OK, 0 rows affected, 2 warnings (0.05 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
On mysql:
mysql> select * from t;
+------+------------+
| id | name |
+------+------------+
| 1 | bruce.song |
| 2 | bruce.song |
+------+------------+
2 rows in set (0.01 sec)
mysql> insert into t select * from t;
Query OK, 2 rows affected (0.03 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t select * from t;
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> On slave:
mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from t;
+------+------------+
| id | name |
+------+------------+
| 1 | bruce.song |
| 2 | bruce.song |
| 1 | bruce.song |
| 2 | bruce.song |
+------+------------+
4 rows in set (0.00 sec)
mysql> select * from t;
+------+------------+
| id | name |
+------+------------+
| 1 | bruce.song |
| 2 | bruce.song |
| 1 | bruce.song |
| 2 | bruce.song |
| 1 | bruce.song |
| 2 | bruce.song |
| 1 | bruce.song |
| 2 | bruce.song |
+------+------------+
8 rows in set (0.00 sec)
mysql> OVER!
操作时遇到一个错误：Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
解决方法如下：
错误现象：
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.20.111
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 500
Relay_Log_File: slave-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000005
Slave_IO_Running: No
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 500
Relay_Log_Space: 120
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 1593
Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID:
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp: 160114 06:27:53
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
mysql>[root@mysql ~] $ cat /var/lib/mysql/auto.cnf
[auto]
server-uuid=b7f4b226-af2b-11e5-83c9-000c29ac69e3
[root@mysql ~] $
[root@slave mysql] $ cat auto.cnf
[auto]
server-uuid=b7f4b226-af2b-11e5-83c9-000c29ac69e3
[root@slave mysql] $
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.20.111
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 500
Relay_Log_File: slave-relay-bin.000005
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000005
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 500
Relay_Log_Space: 456
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: b7f4b226-af2b-11e5-83c9-000c29ac69e3
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
mysql> 
63
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
