configuring MySQL master and slave under Linux

For experimentation convenience, we configure two MySQL services on the same machine (two ports open)

1. Install and configure MySQL

MySQL has been installed beforehand;

[[email protected] ~]# cd/usr/local/[[email protected] local]# cp-r mysql/mysql_2[[email protected] local]# CD mysql_2/

Initialize the MYSQL2, if two "OK" appears and the generated/DATA/MYSQL2 directory description is correct;

[Email protected] mysql_2]#/scripts/mysql_install_db--user=mysql--datadir=/data/mysql2

Copy configuration file

[Email protected] mysql_2]# cp/etc/my.cnf./my.cnf

Modify configuration file related parameters, change port and socket, and add DATADIR=/DATA/MYSQL2

[Email protected] mysql_2]# VI my.cnf [mysqld]port = 3307socket =/tmp/mysql2.sockdatadir=/data/mysql 2

Start:

[Email protected] mysql_2]#/usr/local/mysql_2/bin/mysqld_safe--defaults-file=/usr/local/mysql_2/my.cnf--user= MySQL &

If you want to start the boot, you need to add to the/etc/rc.local;

# echo "/usr/local/mysql_2/bin/mysqld_safe--defaults-file=/usr/local/mysql_2/my.cnf--user=mysql &" >>/ Etc/rc.d/rc.local

Netstat view there are already 2 mysqld services:

[[email protected] mysql2]# netstat -nlp |grep mysqldtcp         0      0 0.0.0.0:3306                 0.0.0.0:*      listen      1203/mysqld    tcp         0      0 0.0.0.0:3307                 0.0.0.0:*     listen       1744/mysqld    unix  2       [ acc ]     stream     listening      8804   1203/mysqld   /tmp/mysql.sockunix   2   &nbSp;  [ acc ]     stream     listening      14159  1744/mysqld   /tmp/mysql2.sock

2. Configure master-Slave preparation work

Set Mysql_2 primary (master) port 3307,mysql to slave (slave) port to 3306

Log in to master MySQL using the sock file

[Email protected] mysql2]#/usr/local/mysql_2/bin/mysql-uroot-s/tmp/mysql2.sock

-S after specifying the MySQL socket file path, which is also a way to log on to MySQL, because on a server ran two MySQL port, so, can only use the-S method to differentiate.

Create a test library DB1

Mysql> CREATE DATABASE Db1;mysql> quit

Export the master MySQL library data and import it to DB1

[[email protected] mysql2]#/usr/local/mysql_2/bin/mysqldump-uroot-s/tmp/mysql2.sock mysql > 123.sql[[email Protected] mysql2]#/usr/local/mysql_2/bin/mysql-uroot-s/tmp/mysql2.sock DB1 < 123.sql

3. Configuration Master (Master)

Vim/usr/local/mysql_2/my.cnf

In the [mysqld] section, see if you have the following, and if not, add:

Server-id=1

Log-bin=mysql-bin

Two optional parameters (2 Select 1):

BINLOG-DO-DB=DB1,DB2 #需要同步的库

BINLOG-IGNORE-DB=DB1,DB2 #忽略不同步的库

Binlog-do-db= needs to replicate the database name, multiple database names, separated by commas. Binlog-ignore-db= does not need to replicate database library names, multiple database names, separated by commas.

After modifying the configuration file, restart Mysql_2

[[email protected] mysql2]# pid= ' ps aux |grep mysql2.sock |grep-v grep |awk ' {print $} ' [[email protected] mysql2]# Kill $pid [[email protected] mysql_2]#/usr/local/mysql_2/bin/mysqld_safe--defaults-file=/usr/local/mysql_2/my.cnf-- User=mysql &

To set the root password:

[Email protected] mysql2]#/usr/local/mysql_2/bin/mysqladmin-uroot-s/tmp/mysql2.sock password ' 123456 ' [[Email Protected] mysql2]#/usr/local/mysql_2/bin/mysql-uroot-s/tmp/mysql2.sock-p123456

mysql> Grant Replication Slave on * * to ' repl ' @ ' 127.0.0.1 ' identified by ' 123123 ';

The REPL here is for the slave end of the user to access the master-side MySQL data, the password is 123123, here the 127.0.0.1 for slave IP (the test configuration of the master and slave are in this machine).

mysql> flush Privileges; Refresh the library, the memory data is written to disk;

Mysql> flush tables with read lock;

Locks the database and does not allow changes to any data at this time

Mysql> Show Master status;+------------------+----------+--------------+------------------+| File | Position | binlog_do_db | binlog_ignore_db |+------------------+----------+--------------+------------------+|      mysql-bin.000004 |              378 |                  | |+------------------+----------+--------------+------------------+

Check the status, the data is to be recorded, one will be used at the slave end;

4, set from (slave)

VIM/ETC/MY.CNF #修改或增加

Server-id = 2 #这个数值不能和主一样

Optional Parameters: REPLICATE-DO-DB=DB1,DB2

REPLICATE-IGNORE-DB=DB1,DB2 #意义同主的那两个可选参数

Restart from the MySQL service: Services mysqld restart

Copy the master's DB1 library data to from:

Pilot out the Db1.sql file, and then create the same database DB1 from the database, import the main db1.sql file to the slave;

[Email protected] ~]#/usr/local/mysql_2/bin/mysqldump-uroot-s/tmp/mysql2.sock-p123456 db1 > Db1.sql[[email prote CTED] ~]#/usr/local/mysql/bin/mysql-uroot-s/tmp/mysql.sock-e "CREATE Database DB1" [[email protected] ~]#/usr/local/m Ysql/bin/mysql-uroot-s/tmp/mysql.sock DB1 < Db1.sql

Log in from MySQL

[Email protected] ~]#/usr/local/mysql/bin/mysql-uroot-s/tmp/mysql.sock mysql> slave stop;mysql> change master T o master_host= ' 127.0.0.1 ', master_port=3307,master_user= ' repl ', master_password= ' 123123 ', master_log_file= ' Mysql-bin.000004 ', master_log_pos=378;

Master_log_file= ' mysql-bin.000004 ', master_log_pos=378 the first 2 columns of the main show master status;

mysql> slave start;

Lord, unlock table:

[Email protected] ~]#/usr/local/mysql_2/bin/mysql-uroot-s/tmp/mysql2.sock-p123456-e "Unlock Tables"

Login from

[Email protected] ~]#/usr/local/mysql/bin/mysql-uroot-s/tmp/mysql.sock

View the status from show slave status\g;

Verify that the following two parameters are yes:

Slave_io_running:yes

Slave_sql_running:yes

mysql> show slave status\g;*************************** 1. row ***************                slave_io _state: waiting for master to send event                   Master_Host: 127.0.0.1                   master_ user: repl                   Master_Port: 3307                 Connect_Retry: 60               Master_Log_File: mysql-bin.000004           read_master_log_pos: 378               relay_ log_file: localhost-relay-bin.000002                 relay_log_pos: 251        relay_ master_log_file: mysql-bin.000004              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: 378               Relay_Log_Space: 410               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:  0master_ssl_verify_server_cert: no                 Last_IO_Errno: 0                 Last_IO_Error:                Last_SQL_Errno: 0                last_sql_error: 

5. Test Master and slave

The Lord emptied the DB table of the DB1 library:

Login Master

[[email protected] ~]# mysql-uroot-s/tmp/mysql2.sock-p123456mysql> use db1;mysql> Select COUNT (*) from db;+----- -----+|        COUNT (*) |+----------+| 2 |+----------+

Clears the DB table;

mysql> TRUNCATE TABLE Db;mysql> Select COUNT (*) from DB; +----------+|        COUNT (*) |+----------+| 0 |+----------+

Enter slave, view the DB1 Library DB table, and the same as the main data, indicating synchronization OK;

[[email protected] ~]# mysql-uroot-s/tmp/mysql.sock mysql> use db1;mysql> Select COUNT (*) from db;+----------+|        COUNT (*) |+----------+| 0 |+----------+

Log in master, delete table db;

[[email protected] ~]# mysql-uroot-s/tmp/mysql2.sock-p123456mysql> drop table db;

Log in From, view DB table does not exist;

[Email protected] ~]# mysql-uroot-s/tmp/mysql.sock mysql> select * from DB; ERROR 1146 (42S02): Table ' db1.db ' doesn ' t exist

Recommendation: MySQL master-slave mechanism is relatively fragile, cautious operation. If you restart Master, be sure to stop the slave first, that is, you need to slave up the slave Stop command, and then go to restart the master MySQL service, otherwise it is likely to be interrupted. Of course, after the restart, you also need to slave to open slave start.

This article is from the "Model Student's Learning blog" blog, please be sure to keep this source http://8802265.blog.51cto.com/8792265/1655877

configuring MySQL master and slave under Linux