欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

Mysql Replication机制主从备份实践_MySQL

程序员文章站 2022-03-28 09:13:37
...

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;