mysql 主从实践

今天要在两天全新服务器上搭建mysql主从复制，在这里记录一下过程。

通过 hostname 命令查看主机名

通过修改 /etc/sysconfig/network  文件 可以修改主机名

1.Setting the Replication Master Configuration

[mysqld]
log-bin=mysql-bin
server-id=1

innodb_flush_log_at_trx_commit=1

sync_binlog=1

commets:

if your master is also a slave (DB1 is the master of DB2, DB2 is the master of DB3) in order for DB2 to log updates from his master DB1 to the binlog (so DB3 can read them) you need to put "log-slave-updates" on my.cnf or my.ini

2.Setting the Replication Slave Configuration

[mysqld]
server-id=2

comments:

You should also put

report-host=hostname

into your my.cnf on the slave, so that the 'show slave hosts' command will work on the master.

In the mysql.log file it's say that we need to setup relay-log in case the server hostname change.

# Replication
server-id=2
relay-log=mysqld-relay-bin

3.Creating a User for Replication

mysql> CREATE USER 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.mydomain.com';

4.Obtaining the Replication Master Binary Log Coordinates

mysql > SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 73       | test         | manual,mysql     |
+------------------+----------+--------------+------------------+

5.Setting Up Replication with New Master and Slaves

To set up replication between a new master and slave:

1. Configure the MySQL master with the necessary configuration properties. See Section 17.1.1.1, “Setting the Replication Master Configuration”.

2. Start up the MySQL master.

3. Set up a user. See Section 17.1.1.3, “Creating a User for Replication”.

4. Obtain the master status information. See Section 17.1.1.4, “Obtaining the Replication Master Binary Log Coordinates”.

5. On the master, release the read lock:

   mysql> UNLOCK TABLES;
   

6. On the slave, edit the MySQL configuration. See Section 17.1.1.2, “Setting the Replication Slave Configuration”.

7. Start up the MySQL slave.

8. Execute a CHANGE MASTER TO statement to set the master replication server configuration. See Section 17.1.1.10, “Setting the Master Configuration on the Slave”.

Perform the slave setup steps on each slave. 

6.Introducing Additional Slaves to an Existing Replication Environment

To add another slave to an existing replication configuration, you can do so without stopping the master. Instead, set up the new slave by making a copy of an existing slave, except that you configure the new slave with a different server-id value. 

1. Shut down the existing slave:

   shell> mysqladmin shutdown