MySQL Master-slave configuration

MySQL master-slave configuration

Replication master-Slave, keep two database data consistent

Master A data changes, generate logs, sent to from b,b According to the log to make corresponding changes, so that the data and A consistent. keeping A,B databases consistent

In reality, the database master and slave in two devices to do, is now convenient and save resources, I did on the same platform, on two platforms to do, configuration similar

[Email protected] ~]# ls/usr/local/

bin/games/lib/sbin/src/

Etc/include/libexec/share/

[[email protected] ~]# rm-rf/usr/local/mysql// If the database is installed, first delete the original

[Email protected] ~]# cd/usr/local/src/

[[email protected] src]# ls

Mysql-5.1.40-linux-i686-icc-glibc23.tar.gz

[Email protected] src]# tar zxvfmysql-5.1.40-linux-i686-icc-glibc23.tar.gz

[Email protected] src]# Mvmysql-5.1.40-linux-i686-icc-glibc23/usr/local/mysql

[[email protected] src]# grep "MySQL"/etc/passwd

[Email protected] src]# useradd-s/sbin/nologinmysql

[Email protected] src]# cd/usr/local/mysql/

[[email protected] mysql]# ls

[Email protected] mysql]# CPSUPPORT-FILES/MY-SMALL.CNF/ETC/MY.CNF

Cp:overwrite '/etc/my.cnf '? Y

[Email protected] mysql]# VIM/ETC/MY.CNF

[Email protected] mysql]# Cpsupport-files/mysql.server/etc/init.d/mysqld

[Email protected] mysql]# Vim/etc/init.d/mysqld

basedir=/usr/local/mysql

datadir=/data/mysql

[[email protected] mysql]# rm-rf/data/mysql// If it was previously installed, it will be deleted .

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

[Email protected] mysql]#/etc/init.d/mysqldstart

[[Email protected] mysql]# PS aux |grep mysql

Root 3694 0.0 0.1 5060 1344 pts/2 S 21:02 0:00/bin/sh/usr/local/mysql/bin/mysqld_safe--datadir=/data/mysq L--pid-file=/data/mysql/client.pid

MySQL 3793 0.9 1.2 102904 13380 pts/2 Sl 21:02 0:00/usr/local/mysql/bin/mysqld--basedir=/usr/local/mysql--dat Adir=/data/mysql--user=mysql--log-error=/data/mysql/client.err--pid-file=/data/mysql/client.pid--socket=/tmp/ Mysql.sock--port=3306

[Email protected] mysql]# NETSTAT-LNP |grep3306

TCP 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 3793/mysqld

The master MySQL installation was successful and started the

now install a second MySQL from MySQL.

[Email protected] mysql]# CD.

[[email protected] local]# ls

[Email protected] local]# cp-r Mysqlmysql_slave

[Email protected] local]# CD mysql_slave/

[Email protected] mysql_slave]# cp/etc/my.cnf. Copy the configuration file to the current directory

[Email protected] mysql_slave]# pwd

/usr/local/mysql_slave

[Email protected] mysql_slave]# vim my.cnf

[Mysqld]

Port = 3307

Socket =/tmp/mysql_slave.sock

DataDir =/data/mysql_slave

[Email protected] mysql_slave]#./scripts/mysql_install_db--user=mysql--datadir=/data/mysql_slave

[Email protected] mysql_slave]# ls/data/mysql_slave/

MySQL Test

[Email protected] mysql_slave]# cd/etc/init.d/

[email protected] init.d]# CP mysqld Mysqldslave

basedir=/usr/local/Mysql_slave

datadir=/data/Mysql_slave

CONF=/ETC/MY.CNF changed to conf=$basedir/my.cnf

[[email protected] init.d]#/etc/init.d/mysqldslave start

[[Email protected] init.d]# PS aux |grep mysql

[Email protected] init.d]# NETSTAT-LNP |grepmysql

TCP 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 3793/mysqld

TCP 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 4105/mysqld

two MySQL has been started

now Configure master-slave, main 3306, from 3307

[[Email protected] init.d]# CD

[[email protected] ~]# MySQL// If no this command is available /usr/local/mysql/bin/mysql

-bash:mysql:command not found

[Email protected] ~]#/usr/local/mysql/bin/mysql

Mysql> quit

Bye

[Email protected] ~]# Vim/etc/profile.d/path

Export path= $PATH:/usr/local/mysql/bin

[Email protected] ~]# source!$

Source/etc/profile.d/path

[[email protected] ~]# mysql// default login 3306

Mysql> quit

[Email protected] ~]# mysql-s/tmp/mysql.sock// log in to 3306

Mysql> quit

[Email protected] ~]# mysql-s/tmp/mysql_slave.sock// log in to 3307

Mysql> quit

[[email protected] ~]# mysql-h127.0.0.1-p3307// log in to the specified listening port

[[email protected] ~]# mysql// login

mysql> CREATE DATABASE db1; Create a table

Mysql> quit

[[email protected] ~]# mysqldump-s/tmp/mysql.sock mysql>123.sql// copy a library to a file

[[email protected] ~]# vim 123.sql// View

[[email protected] ~]# mysql-s/tmp/mysql.sockdb1 <123.sql// copy files to library

[[email protected] ~]# MySQL

mysql> use DB1;

Mysql> Show tables; Library has been copied

Mysql> quit

[Email protected] ~]# VIM/ETC/MY.CNF

Server-id = 1// guaranteed to be different from top Server-id

log-bin=Wang // custom Log-bin

#binlog-do-db=db1,db2//DB1,DB2 only for the master (remove the comments to take effect)

#binlog-ignore-db=mysql// No need to master from the library (remove the comment to take effect)

[Email protected] ~]#/etc/init.d/mysqld restart

[Email protected] ~]# Ls/data/mysql

Wang.index wang.000001//log-bin file

[[email protected] ~]# MySQL

mysql> grant replication Slave on * * to ' repl ' @ ' 127.0.0.1 ' identified by ' 123123 ';

Set Permissions

mysql> flush Privileges; Refresh

Mysql> flush tables with read lock; lock dead, read

Mysql> Show master status; View

+-------------+----------+--------------+------------------+

| File | Position | binlog_do_db | binlog_ignore_db |

+-------------+----------+--------------+------------------+

|      wang.000001|              315|                  | |

+-------------+----------+--------------+------------------+

From top:

[Email protected] ~]# VIM/USR/LOCAL/MYSQL_SLAVE/MY.CNF

Server-id = 111 // set to different from main

[[email protected] ~]# ls

123.sql

[Email protected] ~]# mysql-s/tmp/mysql_slave.sock-e "CREATE Database DB1";

Create a library from the DB1

[Email protected] ~]# Mysql-s/tmp/mysql_slave.sock db1 <123.sql;

Import Library

[Email protected] ~]# mysql-s/tmp/mysql_slave.sock// log in from

mysql> slave stop;

mysql> Change Master to master_host= ' 127.0.0.1 ', master_port=3306, master_user= ' repl ', master_password= ' 123123 ', Master_log_file= ' wang.000001 ', master_log_pos=315;

Master_log_file= ' wang.000001 ', master_log_pos=315; The value is mysql> show master status; See in the

mysql> slave start;

Mysql> show Slave status\g;

Slave_io_running:no

Slave_sql_running:yes

a two key value of Yes indicates a successful configuration and is not configured correctly

mysql> slave stop;

[Email protected] ~]#/etc/init.d/mysqldslaverestart

[Email protected] ~]# Mysql-s/tmp/mysql_slave.sock

mysql> slave start;

Mysql> Show Slave Status\g

Slave_io_running:yes

Slave_sql_running:yes

all two items are yes. the configuration was successful.

Validation test:

Our Lord

[[email protected] ~]# MySQL

mysql> unlock tables;

mysql> use DB1;

Mysql> Show tables;

Help_category

mysql> drop table help_category; Delete the table

[Email protected] ~]# Mysql-s/tmp/mysql_slave.sock

Mysql> Show Slave Status\g

Slave_io_running:yes

Slave_sql_running:yes

mysql> use DB1;

Mysql> Show tables;

no help_category table, after the Lord deleted, from the top sync

Note: The master-slave mechanism cannot be manipulated from the top

Our Lord

mysql> drop Database db1;

Mysql> Show Database;

From top:

mysql> show databases;

DB1 was also deleted

in reality, master-slave is prone to failure, can monitor two Yes values, to interpret whether the master-slave is normal

This article is from the Linux Learning Notes blog, so be sure to keep this source http://9656134.blog.51cto.com/9646134/1678114

MySQL Master-slave configuration