MySQL master-slave replication database

MySQL master-slave replication is believed to have been used a lot, but because of the work of the reason has not been used. While this period of time is relatively idle, it will be realized again. Although there are a lot of similar articles on the Internet, it is still worth keeping track of what is being achieved.

Environment:

Primary server: CentOS 6.0 mysql 5.1.67-log ip:192.168.0.107
From server: CentOS 6.0 mysql 5.1.67-log ip:192.168.0.109
Primary Server Test Database

CREATE TABLE ' menber ' (' name ' varchar (255) default null default ', ' id ' int (one) ' NOT null auto_increment,primary KEY (' id ') ) Engine=myisam auto_increment=1 DEFAULT charset=latin1;insert into ' menber ' (' name ', ' ID ') VALUES (' Zhangsan ', ' 1 '); Insert INTO ' menber ' (' name ', ' ID ') VALUES (' Lisi ', ' 2 ') and insert INTO ' menber ' (' name ', ' ID ') VALUES (' Harry ', ' 3 ');

MySQL default profile, as not specifically specified by default to/ETC/MY.CNF

mysql configuration file lookup order:/etc/my.cnf/etc/mysql/my.cnf ~/.my.cnf

Configuration:

First, the primary server

1.1, create a replication user, with replication slave permissions.

Mysql>grant replication Slave on * * to ' repl ' @ ' 192.168.0.109 ' identified by ' repl '; mysql>flush privileges;

1.2. Edit the My.cnf file

Vi/etc/my.cnf

Add to

server-id=107

and turn on the Log-bin binary log file (MySQL requires read and write access to the/var/lib/mysql/directory "can be changed by chown-r mysql:mysql/var/lib/mysql Command")

Log-bin=/var/lib/mysql/mysql-bin

#指定绝对路径, no person will appear MySQL run show master status, when unable to view log condition mysql> show master status; Empty Set (0.00 sec) mysql> Show binary logs; ERROR 1381 (HY000): You is not using binary logging

Additional extension Configuration items:

BINLOG-DO-DB=MYSQL1 #需要备份的数据库名, if you back up multiple databases, set this option repeatedly
Binlog-ignore-db=mysql2 #不需要备份的数据库名, if you back up multiple databases, set this option repeatedly
Log-slave-updates=1 #这个参数一定要加上, otherwise the updated records will not be sent to the binary file.
Slave-skip-errors=1 #是跳过错误, continue with the copy operation (optional)

1.3. Restart MySQL Database

Service mysqld Restart

1.4. Set Read lock

Mysql>flush tables with read lock;

1.5. Get the Binlog log file name and offset ( remember file names and position values here, you need to use them later slave server configuration )

Mysql> Show Master status;+------------------+----------+--------------+------------------+| File             | Position | binlog_do_db | binlog_ignore_db |+------------------+----------+--------------+------------------+| mysql-bin.000001 |      713 |              |                  | +------------------+----------+--------------+------------------+1 row in Set (0.00 sec)

　　　　

1.6. Back up the database you want to synchronize

Mysqldump Test > Test.sql

1.7. Unlocking

Mysql>unlock tables;

　　　　

Second, from the server (192.168.0.109)

Restore the Master (192.168.0.107) backed-up database data to the slave slave server (192.168.0.109)

2.1. Edit the My.cnf file

Vi/etc/my.cnf

Add to

server-id=109

2.2. Restart from the database

Service mysqld Restart

2.3, the corresponding settings from the database

　　Note the name and position value of the logfile, and the remaining host, user, and password account and password set for the primary database

mysql> stop Slave;
Query OK, 0 rows Affected (0.00 sec)

Mysql> Change Master to
Master_host= ' 192.168.0.107 ',
Master_user= ' Repl ',
Master_password= ' Repl ',
Master_log_file= ' mysql-bin.000001 ',
master_log_pos=713;

mysql> start slave;
Query OK, 0 rows Affected (0.00 sec)

Mysql> show Slave status\g;
1. Row ***************************
Slave_io_state:waiting for Master to send event
master_host:192.168.0.107
Master_user:repl
master_port:3306
Connect_retry:60
master_log_file:mysql-bin.000001
read_master_log_pos:1079
relay_log_file:mysqld-relay-bin.000004
relay_log_pos:251
relay_master_log_file:mysql-bin.000001
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:1079
relay_log_space:407
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.00 sec)

ERROR:
No query specified

Here the main is to see:

Slave_io_running=yes
Slave_sql_running=yes

If Slave_io_running:no or Slave_sql_running:no is present, you need to redo 2.3, set the appropriate settings from the database

Third, test:
You can view the status of the master and slave threads when the above items are configured. On master, you can see the connection created by the slave I/O thread: Enter show processlist\g on master;

Mysql> Show processlist\g;*************************** 1. Row ***************************     id:4   user:root   host:localhost     db:NULLCommand:Query   time:0< C11/>state:null   info:show processlist*************************** 2. Row ***************************     Id:   user:repl   host:192.168.0.109:42337     db:NULLCommand:Binlog Dump   time:183  State:has Sent all binlog to slave; Waiting for Binlog to is updated   info:null2 rows in Set (0.00 sec) Error:no query specified

3.1. Add new data on the main database: 192.168.0.107

Insert INTO ' menber ' (' name ') VALUES (' Li Ba Luyi '), insert INTO ' menber ' (' name ') VALUES (' Cang jing Empty ');

3.2 Viewing the database from the database: 192.168.0.109

Mysql> SELECT * from menber;+-----------+----+| Name      | ID |+-----------+----+| Zhangsan  |  1 | | Lisi      |  2 | | Harry    |  3 | | Li Ba luyi    |  4 | | Cang Jing Empty |  5 |+-----------+----+5 rows in Set (0.02 sec)

At this point the data has been successfully copied to slave from the database 192.168.0.109.

Mysqldump-ubackup-p123456-h 192.168.1.2 backup_test > D:\bak\bakcup.sql

In this section of the command:

Mysqldump is the program name of Myqldump;

-U is the user name of the database, followed by the user name backup;

-P is the password of the database, followed by the same password, note that there is no space between-p and password;

-H is the address of the database and, if not, indicates that the database is backed up locally;

Backup_test the name of the database to be backed up;

> indicates that the database is backed up to a later file, followed by the address of the backup file of course, note that to use the absolute file path, the suffix of the file can also be used. txt.

Once the command is executed, Mysqldump immediately performs the backup operation. Remotely backing up a database it would take a while for a typical remote backup to look like a network.
Actually, just remember the two orders.

Export:
(Entire database)
Mysqldump-u root-p Database > Data.sql
(Single table)
Mysqldump-u root-p Database tablename > Data.sql

Import:
Mysql-u Root-p Database < Data.sql

MySQL master-slave replication database