Mysql Replication mechanism master-slave backup practices

Mysql Replication mechanism master-slave backup practices
Mysql Replication mechanism master-slave backup practices reference:

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

(If "media change: please insert the disc labeled" exists during mysql installation, solution: 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)

Procedure:

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 <dbdump. db

7. Setting the Master Configuration on the Slave server2:
Mysql> CHANGE MASTER
-> 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 '20140901 ';
> 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;