MySQL Master-slave configuration

In the original LNMP environment

[Email protected] ~]# vim/etc/init.d/mysqld #查找数据库的安装路径

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

[[email protected] mysql]# ls

[Email protected] mysql]# tar czvf discuz.tar/data/mysql/discuz/

#备份之前的discuz Forum database table.

-----------------------------------------------------------------------

Install MySQL:

[Email protected] mysql]# Rm-rf/usr/local/mysql #删除之前的安装

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

[Email protected] src]# tar zxvf mysql-5.1.73-linux-x86_64-glibc23.tar.gz

[Email protected] src]# MV Mysql-5.1.73-linux-x86_64-glibc23/usr/local/mysql

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

Bin COPYING data docs include Install-binary lib man mysql-test README scripts share Sql-bench support-files

[[email protected] src]# grep ' mysql '/etc/passwd #检查有没有 mysql This user, if not created

Mysql:x:500:500::/home/mysql:/sbin/nologin

[email protected] mysql]# CP support-files/my-small.cnf/etc/my.cnf #拷贝配置文件

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

[email protected] mysql]# CP support-files/mysql.server/etc/init.d/mysqld

Cp:overwrite '/etc/init.d/mysqld '? Y

[Email protected] ~]# vim/etc/init.d/mysqld #编译mysql配置文件

Basedir=/usr/local/mysql #安装路径

Datadir=/data/mysql #数据库路径

[Email protected] mysql]# rm-rf/data/mysql #删除原来的/datadir

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

#初始化, you see the following two OK, the installation is complete

[[email protected] mysql]# echo $?

0

[Email protected] mysql]# Ls/data/mysql #安装无误会生成这两个目录

MySQL Test

650) this.width=650; "src=" http://s2.51cto.com/wyfs02/M02/8B/75/wKiom1hOccXyoN-tAAC2hU9Ivrg893.jpg "title=" q2.jpg "alt=" Wkiom1hoccxyon-taac2hu9ivrg893.jpg "/>

[Email protected] ~]#/etc/init.d/mysqld start #启动mysql

[[Email protected] ~]# PS aux |grep mysql #查看

[Email protected] ~]# NETSTAT-LNP |grep 3306

650) this.width=650; "src=" http://s5.51cto.com/wyfs02/M00/8B/70/wKioL1hOZ32TEmyDAAFlcvOyU_E924.jpg "title=" q1.jpg "alt=" Wkiol1hoz32temydaaflcvoyu_e924.jpg "/>

----------------------------------------Split Line----------------------------------------

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

[[email protected] local]# ls

Bin etc games include jdk1.8.0_112 Lib lib64 libexec Mans mysql nginx php pureftpd resin sbin share src to MCat

[[email protected] local]# cp-r mysql mysql_slave #创建第二个mysql (copy)

[Email protected] mysql_slave]# chown-r Mysql:mysql/data/mysql_slave #

[Email protected] mysql_slave]# cp/etc/my.cnf. #拷贝配置文件到mysql_slave under

[Email protected] mysql_slave]# vim my.cnf #编译配置文件

# The MySQL server

[Mysqld]

Port = 3307 #3306 changed to 3307

Socket =/tmp/mysql_slave.sock #/tmp/mysql.sock changed to/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]# cd/etc/init.d/

[email protected] init.d]# CP mysqld Mysqldslave #复制启动文件

[Email protected] init.d]# vim Mysqldslave #编译启动文件

Basedir=/usr/local/mysql_slave #mysql Change to Mysql_slave

Datadir=/data/mysql_slave #mysql Change to Mysql_slave

conf=/$basedir/MY.CNF # (This one can also be written here)

# Try to find Basedir in/etc/my.cnf

conf=/$basedir/my.cnf #更改conf =/etc/my.cnf to conf=/$basedir/my.cnf

-----------------------------------------------------------------------

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

Starting MySQL. success!

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

Starting MySQL. success!

[[email protected] init.d]# NETSTAT-LNP |grep MySQL #两个mysql.

#扩展, if you need to start a MySQL how to solve

？ Reconfigure the configuration as you start.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/8B/79/wKiom1hOrtvixaJYAAFXCy7jjDk048.jpg "title=" q3.jpg "alt=" Wkiom1hortvixajyaafxcy7jjdk048.jpg "/>

(Because the landlord is LNMP environment down to build, in the process of some errors, because the landlord is a beginner small white, wrong experience less points, this before a two problems, one is the MySQL account can not be logged, and the second is not a successful MySQL initialization.

MySQL can not login, is not empowered, empowerment is good [[email protected] mysql_slave]# chown-r mysql:mysql/data/mysql_slave. Initialization not successful )

---------------------------------------------------------------------------------

Mysql Master-Slave configuration: The primary is 3306 from IS 3307

Several Fang to log in to MySQL

[[email protected] ~]# which MySQL

/usr/bin/mysql #楼主预装了mysql command.

[[email protected] ~]#/usr/local/mysql/bin/mysql #如果没有预装, log in with the absolute path MySQL

(To map the MySQL installation directory, such as Mysqlpath/bin/mysql, to the/usr/local/bin directory:
# Cd/usr/local/bin
# ln-fs/mysqlpath/bin/mysql MYSQ l)

[Email protected] ~]# mysql-s/tmp/mysql.sock #通过端口登录

[email protected] ~]# MySQL #直接命令登录

[[email protected] ~]# mysql-s/tmp/mysql_slave.sock #3307 Login

[Email protected] ~]# mysql-h127.0.0.1-p3307 #通过端口登, (Big P)

------------------------------------------------------------

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

mysql> CREATE DATABASE db1; #创建一个测试数据库

Query OK, 1 row affected (0.05 sec)

[Email protected] ~]# mysqldump-s/tmp/mysql.sock mysql > 123.sql #拷贝一些测试数据

--warning:skipping The data of table mysql.event. Specify the--events option explicitly.

[Email protected] ~]# mysql-s/tmp/mysql.sock db1 < 123.sql #恢复数据到db1

mysql> use DB1;

Database changed

Mysql> Show tables;

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

| TABLES_IN_DB1 |

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

| Columns_priv |

| db |

| Event |

| Func |

| General_log |

| Help_category |

| Help_keyword |

| help_relation |

| Help_topic |

| Host |

| Ndb_binlog_index |

| Plugin |

| Proc |

| Procs_priv |

| Servers |

| Slow_log |

| Tables_priv |

| Time_zone |

| Time_zone_leap_second |

| Time_zone_name |

| time_zone_transition |

| Time_zone_transition_type |

| user |

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

Rows in Set (0.00 sec)

[Email protected] ~]# vim/etc/my.cnf #配置主的配置文件

Server-id = 1 #打开server-id, the value and the

Og-bin=mysql-bin #打开log-bin, the file name can be customized, the landlord changed to "Rizhi".

BINLOG-DO-DB=DB1 #只针对db1做主从, can be followed by multiple libraries

#扩展 (Binlog-ignore-db=mysql, if there are hundreds of libraries, you can reverse thinking, make a blacklist, specify the unsynchronized library MySQL)

[[email protected] ~]#/etc/init.d/mysqld restart #配置完成后, restart MySQL

Shutting down MySQL ... success!

Starting MySQL. success!

[[email protected] ~]# ls/data/mysql #系统会生成一个mysql-bin-prefixed file

DB1 ibdata1 ib_logfile0 ib_logfile1 lnmp.err lnmp.pid mysql mysql-bin.000001 mysql-bin.index te St

[[email protected] ~]# mysql-s/tmp/mysql.sock #登录主, create user replication: permissions, Repl: Username

mysql> Grant replication slave on * * to 'repl' @ ' 127.0.0.1 ' identified by ' 123456 ';

Query OK, 0 rows Affected (0.00 sec)

mysql> flush Privileges; #刷新权限

Query OK, 0 rows Affected (0.00 sec)

Mysql> flush tables with read lock; #锁死读的权限

Query OK, 0 rows Affected (0.00 sec)

Mysql> Show master status; #查看mrizhi. 0000001 data, which is used when the configuration is synchronized

650) this.width=650; "src=" http://s2.51cto.com/wyfs02/M00/8B/7D/wKiom1hPfuOwVEcRAAB8i11Nilc572.jpg "title=" m1.jpg "alt=" Wkiom1hpfuowvecraab8i11nilc572.jpg "/>

-----------------------------------------

[Email protected] ~]# vim/usr/local/mysql_slave/my.cnf #从的配置

Server-id = 1111 #打开server-id, and custom change 1111

#replicate-DO-DB=DB1 (Extended)

[[email protected] ~]# ls

123.sql anaconda-ks.cfg install.log install.log.syslog opt www.blog-andy.com.conf

[Email protected] ~]# mysql-s/tmp/mysql_slave.sock-e "CREATE Database DB1" #在从上创建db1

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

# to achieve synchronization, make sure the libraries on both machines are the same

[Email protected] ~]# mysql-s/tmp/mysql_slave.sock #登录从

mysql> slave stop; #关掉数据库

Query OK, 0 rows affected, 1 Warning (0.00 sec)

mysql> change Master to master_host= ' 127.0.0.1 ', master_port=3306, master_user= ' repl ', master_password= ' 123456 ', master_log_file= ' rizhi.000001 ', master_log_pos=106;

Query OK, 0 rows affected (0.04 sec)

#这是个关键核心配置, grammar spelling, must be rigorous.

mysql> slave start; #启动从

Query OK, 0 rows affected (0.01 sec)

Mysql> show Slave status\g; #查看状态 #没有配置成功

650) this.width=650; "src=" http://s1.51cto.com/wyfs02/M02/8B/79/wKioL1hPg2mQ1WEwAAFQUgWCNqU098.jpg "title=" m2.jpg "alt=" Wkiol1hpg2mq1wewaafqugwcnqu098.jpg "/>

Check the following error:

last_io_error: Fatal error:the slave I/O thread stops because master and slave have equal MySQL server IDs; These IDs must be different for replication to work (or the--replicate-same-server-id option must is used on slave but th is does do sense; Please check the manual before using it).

[[email protected] ~]#/etc/init.d/mysqldslave Restart #重启从, just modified Server-id no reboot

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

Mysql> show Slave status\g;

650) this.width=650; "src=" http://s1.51cto.com/wyfs02/M01/8B/7D/wKiom1hPhGvhAPijAAD9koCFkPw218.jpg "title=" m3.jpg "alt=" Wkiom1hphgvhapijaad9kocfkpw218.jpg "/>

------------------------------------------------------

Master-Slave Testing:

In the Lord's operation:

(mysql> flush tables with read lock; #因为之前有锁死的操作)

Mysql>unlock tables; #解锁读的权限

mysql> use DB1;

Database changed

Mysql> Show tables;

mysql> drop table help_category;

On the action from:

mysql> use DB1;

Database changed

Mysql> show tables; #详情见, synchronization is successful.

650) this.width=650; "src=" http://s4.51cto.com/wyfs02/M01/8B/7D/wKiom1hPiWqRoOT1AAE-5Bf-Ax4870.jpg "title=" m4.jpg "alt=" Wkiom1hpiwqroot1aae-5bf-ax4870.jpg "/>

Operations in the Lord: Create a table

Mysql> Show CREATE TABLE user\g: # using the user table as an example

Create table:create Table ' user ' (

' Host ' char COLLATE utf8_bin not NULL DEFAULT ' ',

' User ' char (+) COLLATE utf8_bin not NULL DEFAULT ' ',

' Password ' char (CHARACTER SET latin1 COLLATE latin1_bin not NULL DEFAULT ' ',

' Select_priv ' enum (' n ', ' Y ') CHARACTER SET UTF8 not NULL DEFAULT ' N ',

' Insert_priv ' enum (' n ', ' Y ') CHARACTER SET UTF8 not NULL DEFAULT ' N ',

' Update_priv ' enum (' n ', ' Y ') CHARACTER SET UTF8 not NULL DEFAULT ' N ',

' Delete_priv ' enum (' n ', ' Y ') CHARACTER SET UTF8 not NULL DEFAULT ' N ',

' Create_priv ' enum (' n ', ' Y ') CHARACTER SET UTF8 not NULL DEFAULT ' N ',

。。。。。。 Slightly lower

CREATE TABLE ' Linuxt '

(

' Host ' char COLLATE utf8_bin not NULL DEFAULT ' ',

' User ' char (+) COLLATE utf8_bin not NULL DEFAULT ' ',

' Password ' char (CHARACTER SET latin1 COLLATE latin1_bin not NULL DEFAULT ' ',

' Select_priv ' enum (' n ', ' Y ') CHARACTER SET UTF8 not NULL DEFAULT ' N ',

' Insert_priv ' enum (' n ', ' Y ') CHARACTER SET UTF8 not NULL DEFAULT ' N ',

' Update_priv ' enum (' n ', ' Y ') CHARACTER SET UTF8 not NULL DEFAULT ' N ',

' Delete_priv ' enum (' n ', ' Y ') CHARACTER SET UTF8 not NULL DEFAULT ' N ',

' Create_priv ' enum (' n ', ' Y ') CHARACTER SET UTF8 not NULL DEFAULT ' N ',

。。。。。。。。。。。

Engine=myisam DEFAULT Charset=utf8 collate=utf8_bin comment= ' Users and global privileges ';

#在结尾去加分号 ";" In the name of the table names do not have numbers, the landlord started the table name has a number, always error.

mysql> use DB1;

Mysql> Show tables;

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/8B/7F/wKiom1hPm1PzW_X0AAExAIYClBs691.jpg "title=" m5.jpg "alt=" Wkiom1hpm1pzw_x0aaexaiyclbs691.jpg "/>

Delete database operation: The synchronization succeeded on the slave.

650) this.width=650; "src=" http://s5.51cto.com/wyfs02/M02/8B/7F/wKiom1hPndaBkkhhAAFDJPsQSfw775.jpg "title=" m6.jpg "alt=" Wkiom1hpndabkkhhaafdjpsqsfw775.jpg "/>

----------------------------------------------------------------------

MySQL's master-slave mechanism is quite fragile, it is easy to interrupt, the landlord in the course of the experiment, accidentally on the data table from the creation, and finally found the wrong. Back to the Lord now to operate, found that cannot be created, and finally can only restore the data table, and then re-operation.

In the actual production environment, do the monitoring.

Slave_io_running:yes #监控这两项是不是YES

Slave_sql_running:yes

Last_io_error: (see if there are any errors here)

Be sure not to write data from the top, which can cause the database to become disorganized.

This article is from the "Cbo#boy_linux Road" blog, make sure to keep this source http://20151213start.blog.51cto.com/9472657/1882281

MySQL Master-slave configuration