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;