Mysql Replication機制主從同步實踐Mysql Replication機制主從同步實踐



Install mysql:

<a target="_blank" href="https://www.linode.com/docs/databases/mysql/how-to-install-mysql-on-ubuntu-14-04">https://www.linode.com/docs/databases/mysql/how-to-install-mysql-on-ubuntu-14-04</a>

replication mysql:

<a target="_blank" href="https://dev.mysql.com/doc/refman/5.5/en/replication-howto.html">https://dev.mysql.com/doc/refman/5.5/en/replication-howto.html</a>

（如果安裝mysql過程中，有"media change: please insert the disc labeled"，解決方案：sudo sed -i '/cdrom/d' /etc/apt/sources.list

1.Install mysql on 2 server:

apt-get install mysql-server

2.Set binary log and server-id in /etc/mysql/my.cnf

server1:

[mysqld]

log-bin=/var/log/mysql/mysql-bin.log

server-id=1

server2:

server-id=2

3.Using "mysql -u root -p" to connect to mysql, and run below command on slave sever2:

4. Restart mysql on server1 and server2:

service mysql stop

service mysql start

5. Run command on master server1:

mysql&gt; FLUSH TABLES WITH READ LOCK;

mysql&gt; SHOW MASTER STATUS;

+------------------+----------+--------------+------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

| mysql-bin.000001 |      106 |              |                  |

1 row in set (0.00 sec)

6.Creating a Data Snapshot Using mysqldump on master server1:

shell&gt; mysqldump -h 127.0.0.1 -u root -p 123456 --all-databases --master-data &gt; dbdump.db

BTW, if we have new master or slave need to create, we can use shell&gt; mysql -h master &lt; dbdump.db

7.Setting the Master Configuration on the Slave server2:

mysql&gt; CHANGE MASTER TO

    -&gt;     MASTER_HOST='server1',

    -&gt;     MASTER_USER='repl',

    -&gt;     MASTER_PASSWORD='slavepass',

    -&gt;     MASTER_LOG_FILE='mysql-bin.000001',

    -&gt;     MASTER_LOG_POS=106;

8.Unlock tables on master server1:

mysql&gt; UNLOCK TABLES;

9.Grant all permission for root on master server1:

&gt;use mysql

&gt;FLUSH PRIVILEGES;

10.connect to master server1 on slave server2:

mysql -h server1 -u root -p

11.run below commands on slave server2:

mysql&gt; start slave;

Query OK, 0 rows affected (0.01 sec)

mysql&gt; SHOW SLAVE STATUS\G

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 10.185.98.24

                  Master_User: repl

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000003

          Read_Master_Log_Pos: 825

               Relay_Log_File: mysqld-relay-bin.000004

                Relay_Log_Pos: 971

        Relay_Master_Log_File: mysql-bin.000003

             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: 825

              Relay_Log_Space: 1273

              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

mysql&gt;

12. Create a database on master:

mysql&gt; create database test;

13. Check new database test sync to slave:

mysql&gt; show databases;

+--------------------+

| Database           |

| information_schema |

| mysql              |

| performance_schema |

| test               |

4 rows in set (0.00 sec)

BTW, if you want to check all users on master, use command:select User,Host from mysql.user;