Mysql Replication机制主从备份实践_MySQL

Mysql Replication机制主从备份实践

参考资料：

Install mysql:
https://www.linode.com/docs/databases/mysql/how-to-install-mysql-on-ubuntu-14-04
replication mysql:
https://dev.mysql.com/doc/refman/5.5/en/replication-howto.html

（如果安装mysql过程中，有"media change: please insert the disc labeled"，解决方案：sudo sed -i '/cdrom/d' /etc/apt/sources.list
http://askubuntu.com/questions/386265/media-change-please-insert-the-disc-labeled-when-trying-to-install-ruby-on-ra）

具体步骤：

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:
[mysqld]
log-bin=/var/log/mysql/mysql-bin.log
server-id=2

3.Using "mysql -u root -p" to connect to mysql, and run below command on slave sever2:
mysql> CREATE USER repl@'%' IDENTIFIED BY 'slavepass';
mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'%';

4. Restart mysql on server1 and server2:
service mysql stop
service mysql start

5. Run command on master server1:
mysql> FLUSH TABLES WITH READ LOCK;
mysql> 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> mysqldump -h 127.0.0.1 -u root -p 123456 --all-databases --master-data > dbdump.db
BTW, if we have new master or slave need to create, we can use shell> mysql -h master

7.Setting the Master Configuration on the Slave server2:
mysql> CHANGE MASTER TO
-> MASTER_HOST='server1',
-> MASTER_USER='repl',
-> MASTER_PASSWORD='slavepass',
-> MASTER_LOG_FILE='mysql-bin.000001',
-> MASTER_LOG_POS=106;

8.Unlock tables on master server1:
mysql> UNLOCK TABLES;

9.Grant all permission for root on master server1:
>use mysql
>GRANT ALL ON *.* to root@'%' IDENTIFIED BY '123456';
>FLUSH PRIVILEGES;

10.connect to master server1 on slave server2:
mysql -h server1 -u root -p

11.run below commands on slave server2:
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

mysql> 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
1 row in set (0.00 sec)

mysql>

12. Create a database on master:
mysql> create database test;

13. Check new database test sync to slave:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)

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