MySQL Master-slave configuration

One: MySQL master-slave configuration environment

Build MySQL master-slave on the same server

Mysql-5.1.72-linux-i686-glibc23.tar.gz

Second: Building steps

1. Download:  cd/usr/local/src; wget http://mirrors.sohu.com/mysql/MySQL-5.1/mysql-5.1.72-linux-i686-glibc23.tar.gz

2, Decompression:tar zxvf mysql-5.1.40-linux-i686-icc-glibc23.tar.gz;

Move rename: MV Mysql-5.1.40-linux-i686-icc-glibc23. /mysql; Cp-r. /mysql. /mysql2

cd/usr/local/mysql/

3. Copy the configuration file and startup script:

CP./SUPPORT-FILES/MY-SMALL.CNF/ETC/MY.CNF; cp/etc/my.cnf/usr/local/mysql2/

CP./support-files/mysql.server/etc/init.d/mysqld; Cp/etc/init.d/mysqld/etc/init.d/mysqld2

4. mysql Data storage directory: mkdir-p/data/mysql;mkdir-p/data/mysql2

New MySQL User: useradd-s/sbin/nologin mysql

Change directory permissions: Chown-r mysql:mysql/data/mysql; Chown-r MYSQL:MYSQL/DATA/MYSQL2

5, modify the configuration file, start the script

Master MySQL vim/etc/init.d/mysqld modify Basedir datadir

from MySQL vim/usr/local/mysql2/my.cnf; Modify Port socket Server-id (different from main)

Vim/etc/init.d/mysqld2 Modify Basedir DataDir conf

6. mysql Installation

Master MySQL cd/usr/local/mysql/;./scripts/mysql_install_db--user=mysql--datadir=/data/mysql

from MySQL cd/usr/local/mysql2/;./scripts/mysql_install_db--user=mysql--datadir=/data/mysql2

7. Start the main mysql:/etc/init.d/mysqld starting

Modify Master MySQL PW: /usr/local/mysql/bin/mysqladmin-uroot password ' 1 '

New DB: /usr/local/mysql/bin/mysql-uroot-p1-e ' Create Database db1 '

to back up the MySQL database: /usr/local/mysql/bin/mysqldump-uroot-p1 mysql > Mysql.sql

Import DB1 Library:/usr/local/mysql/bin/mysql-uroot-p1 db1 < Mysql.sql

8, modify the main MySQL configuration vim/etc/my.cnf new Log-bin=mysql-bin (custom) BINLOG-DO-DB=MYSQL,DB1, restart

9. Assign user synchronization permissions grant replication Slave on * * to ' WQL ' @ ' 127.0.0.1 ' identified by ' WQL '; flush Privileges;

10, new db1 from MySQL, import mysql.sql into DB1

/usr/local/mysql2/bin/mysqladmin-uroot password ' 2 '-s/tmp/mysql2.sock

/usr/local/mysql2/bin/mysql-uroot-p2-s/tmp/mysql2.sock-e ' Create Database db1 '

/usr/local/mysql2/bin/mysql-uroot-p2-s/tmp/mysql2.sock DB1 < Mysql.sql

11, modify from MySQL configuration vim/usr/local/my.cnf log-bin=mysql-bin (custom) REPLICATE-DO-DB=MYSQL,DB1, restart

12, enter the main MySQL, lock table, check the status;

Flush tables with read lock;

Show master status;

Enter from MySQL

Slave stop;

Change Master to master_host= ' 127.0.0.1 ', master_port=3306, master_user= ' WQL ', master_password= ' WQL ', master_log_file = ' mysql-bin.000002 ', master_log_pos=106;

Slave start;

show slave status;

13, test effect, restart master-slave MySQL

Enter the main MySQL db1 database, delete a table, check in from MySQL, whether this table exists or not, the master-slave configuration succeeds, if it is still, the master-slave configuration is unsuccessful.

Three: The problem

Report the following error:

1, [[email protected]/]# service mysqld start

Starting MySQL. error! Manager of Pid-file quit without updating file.

Added in main MySQL

Log-bin=mysql-bin

Binlog-do-db=database1,database2 or Binlog-ignore-db=database1,database2 two items cause an error

The main reason is that MySQL is not installed and is not executed./script/mysql-install-db--user=mysql--datadir=/data/mysql

2, the configuration is good, master and slave has been unable to synchronize, mainly in the configuration from the following configuration is not as follows

Main MySQL:

Log-bin=mysql-bin

Binlog-do-db=database1,database2 or Binlog-ignore-db=database1,database2

Restart/etc/init.d/mysqld restart

From MySQL:

Log-bin=mysql-bin

Replication-do-db=database1,database2 or Replication-ignore-db=database1,database2

Restart/etc/init.d/mysqld2 restart

MySQL master-slave configuration