-
I. Description of the Environment
Primary database Server ip:192.168.0.2
ip:192.168.0.3 from the database server
MySQL on the master-slave server has been completely installed, but it is ready for production, so no data is generated.
Second, master-slave replication configuration
1. master database Server Master configuration
(1) Slave authorization from the server
mysql> grant replication Slave on *. [email protected]Identified by "123";
(2) View master status
Mysql> Show master status;
+------------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 400 | | |
+------------------+----------+--------------+------------------+
1 row in Set (0.00 sec)
Write down file and position, and a configuration slave will be used. (file for MySQL log files, position is the thread number of the log, from where the server will start copying)
2. Slave configuration from the database server
(1) Modify the server ID of the slave to avoid duplication with other MySQL databases
Vi/etc/my.cnf
Server-id = 2
(2) Perform synchronization of SQL statements
mysql> Change Master to master_host=192.168.0.2, Master_user=slave, master_password=123, master_log_file= mysql-bin.000003, master_log_pos=400;
(3) Start the slave synchronization function
mysql> start slave;
(4) View slave status
Mysql> show slave status G;
1. Row ***************************
Slave_io_state:waiting for Master to send event
master_host:192.168.0.2
Master_user:slave
master_port:3306
Connect_retry:60
master_log_file:mysql-bin.000003
read_master_log_pos:548
relay_log_file:mysql-relay-bin.000002
relay_log_pos:399
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:548
relay_log_space:554
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:
1 row in Set (0.02 sec)
ERROR:
No query specified
Note: Slave_io_running:yes
Slave_sql_running:yes when both the IO and SQL processes are yes,master and Slave, the master-slave replication is possible.
Third, verification test
1. Create a test table on master (root user)
mysql> Use test
Mysql> CREATE TABLE TBS (id int);
mysql> INSERT into TBS values (1);
2. Check if replication succeeded on slave (root user)
mysql> Use test
Mysql> Show tables;
Mysql> select * from TBS;
+------+
| ID |
+------+
| 1 |
+------+
1 row in Set (0.00 sec)
Show Replication Success!
-
Transfer from http://www.2cto.com/database/201107/96109.html
8 MySQL master-slave replication
