MySQL Database build master server configuration

MySQL's master-slave replication is at least two MySQL services, of course, MySQL services can be distributed on different servers, can also start multiple services on a single server.

(1) First to ensure that the master and slave server MySQL version of the same

(2) on the primary server, set up an account from the database, using replication slave to grant permissions, such as:

The code is as follows	Copy Code
Mysql> GRANT REPLICATION SLAVE on *.* to ' slave001′@ ' 192.168.0.99′identified by ' 123456′; Query OK, 0 rows affected (0.13 sec)

(3) Modify the primary database configuration file my.cnf, open the Binlog, and set the Server-id value, after modification must restart the MySQL service

The code is as follows	Copy Code
[Mysqld] Log-bin =/home/mysql/log/mysql-bin.log Server-id=1

(4) The current binary log name and offset of the primary server can then be obtained, which is intended to restore data from this point after it has been started from the database

The code is as follows	Copy Code
Mysql> Show Master STATUSG; 1. Row *************************** file:mysql-bin.000003 position:243 binlog_do_db: binlog_ignore_db: 1 row in Set (0.00 sec)

(5) OK, now can stop the main data update operation, and generate a backup of the primary database, we can through mysqldump everywhere data to from the database, of course, you can also directly with the CP command to copy data files to the database

Note The master database is read LOCK before data is exported to ensure data consistency

The code is as follows	Copy Code
Mysql> flush tables with read lock; Query OK, 0 rows affected (0.19 sec)

And then the mysqldump.

The code is as follows	Copy Code
Mysqldump-h127.0.0.1-p3306-uroot-p Test >/home/chenyz/test.sql

It is best to restore the write operation after the primary database has been backed up

The code is as follows	Copy Code
mysql> unlock tables; Query OK, 0 rows affected (0.28 sec)

(6) Copy the Test.sql of the primary data backup to the database and import

(7) then modify the my.cnf from the database, add the Server-id parameters, specify the users that the replication uses, the IP of the primary database server, the ports, and the files and locations where the replication log starts.

The code is as follows	Copy Code
[Mysqld] server-id=2 Log_bin =/var/log/mysql/mysql-bin.log Master-host =192.168.1.100 Master-user=test master-pass=123456 Master-port =3306 Master-connect-retry=60 Replicate-do-db =test

(8) Starting the slave process from the server
mysql> start slave;

(9) Show Salve status verification from the server

The code is as follows	Copy Code
Mysql> Show SLAVE STATUSG 1. Row *************************** Slave_io_state:waiting for Master to send event Master_host:localhost Master_user:root master_port:3306 Connect_retry:3 master_log_file:mysql-bin.003 read_master_log_pos:79 relay_log_file:gbichot-relay-bin.003 relay_log_pos:548 Relay_master_log_file:mysql-bin. 003 Slave_io_running:yes Slave_sql_running:yes

(10) OK, now you can do some update on our home server, and then see if it has been updated from the server