Shulou(Shulou.com)06/01 Report--

I don't know if you have any knowledge of the previous articles on the detailed steps of configuring MySQL Replication, but I'm here to tell you a little bit about it. If you are interested, let's take a look at the body. I'm sure you'll get something after reading the detailed steps of configuring MySQL Replication.

MySQL Replication is mainly used for real-time backup or read-write separation of MySQL. You can also configure two mysql CVMs or two ports on one CVM before configuration.

Process diagram:

Amure-> change data-- > bin_log-- > transfer-- > Bmuri-> repl_log-- > change data

First, set up a mysql and run port 3306.

1. Download mysql to / usr/local/src/

Wget http://mirrors.sohu.com/mysql/MySQL-5.6/mysql-5.6.35-linux-glibc2.5-x86_64.tar.gz

2. Decompress

Tar zxvf mysql-5.6.35-linux-glibc2.5-x86_64.tar.gz

3. Move the extracted data to / usr/local/mysql

Mv mysql-5.6.35-linux-glibc2.5-x86_64 / usr/local/mysql

4. Establish mysql user

Useradd-s / sbin/nologin mysql

5. Initialize the database

Cd / usr/local/mysqlmkdir-p / data/mysqlchown-R mysql:mysql / data/mysql./scripts/mysql_install_db-- user=mysql-- datadir=/data/mysql

6. Copy configuration fil

Cp support-files/my-large.cnf / etc/my.cnf copy configuration file

7. Copy the startup script file and modify its properties

Cp support-files/mysql.server / etc/init.d/mysqldchmod 755 / etc/init.d/mysqld

8. Modify startup script

Vim / etc/init.d/mysqld

There is "datadir=/data/mysql" that needs to be modified.

Basedir=/usr/local/mysql

9. Add the startup script to the system service item, and set the boot to start, start mysql

Chkconfig-add mysqldchkconfig mysqld on or service mysqld start or / etc/init.d/mysqld start

The command to check whether mysqld is started is: ps aux | grep mysqld

2. Build a mysql with port 3307 below:

[root@localhost ~] # mkdir / data/mysql_ slave [root @ localhost ~] # chown-R mt=ysql:mysql / data/mysql_ slave [root @ localhost ~] # cd / usr/local/ [root@localhost local] # cp-r mysql mysql_ slave [root @ localhost local] # cd mysql2 [root@localhostmysql_slave] #. / scripts/mysql_install_db-user=mysql-datadir=/data/mysql_slave [root@localhostmysql_slave] # cp / etc/my.cnf . [root@localhost mysql_slave] # vim my.cnf [mysqld] change to: port = 3307 to: socket = / tmp/mysql_slave.sock add another line at the bottom of this line: datadir= / data/mysql_slave

After saving it, you can start it:

[root@localhost mysql_slave] # cd bin/ [root@localhost bin] #. / mysqld_safe-- defaults-file=../my.cnf-- user=mysql & restart: pid= `ps aux | grep mysql2.sock | grep-v grep | awk'{print $2}'`; kill $pid;cd / usr/local/mysql2/bin/;. / mysqld_safe-- default-file=../my.cnf-- user=mysql &

If you boot, you need to add the startup command to / etc/rc.local

If you want to start the script like mysqld, then:

[root@localhost mysql_slave] # cd / etc/init.d/ [root@localhost init.d] # cp mysqld mysqldslavevim mysqldslave change datadir=/data/mysql "to datadir=/data/mysql_slave basedir=/usr/local/mysql to basedir=/usr/local/mysql_slave to conf=/etc/my.cnf to conf=$basedir/my.cnf

Can also be configured to / etc/init.d/mysqlslave

Cp-r mysql mysql_slave

The same modification

Basedir=/usr/local/mysql_slavedatadir=/data/mysql_slaveconf=$basedir/my.cnf

The following conf path

Conf=$basedir/my.cnf

But you can't start with / etc/init.d/mysqlslave start

You can use service mysqlslave start

[root@hardman bin] # chkconfig-- add mysqldslave [root@hardman bin] # chkconfig mysqldslave on [root@hardman bin] # chkconfig-- add mysqld [root@hardman bin] # chkconfig mysqld on

You can also write to the / etc/rc.d/rc.local file:

Echo ". / mysqld_safe-- defaults-file=../my.cnf-- user=mysql &" > > / etc/rc.d/rc.local

3. Create a test database on the primary CVM:

3. Create a test database on the primary CVM:

Log in to two mysql

Mysql-h227.0.0.1-P3306 # login via host port mysql-h227.0.0.1-P3307 mysql-S / tmp/mysql.sock # login via sock mysql-S / tmp/mysql_slave.sock mysql-uroot-p password # login by password

Log in to mysql:/usr/local/mysql/bin/mysql-uroot-p199610

Or mysql-S / tmp/mysql.sock

Log in to mysql_slave:mysql-S / tmp/mysql_slave.sock or mysql-h227.0.0.1-P3307 (no secret)

Use port 3306 mysql as master (master) and 3307 mysql as slave (slave)

To make the experiment more like a production environment, create a library db1 on master

[root@localhost bin] # mysql-uroot-S / tmp/mysql.sock-p199610

Mysql > create database db1

Query OK, 1 row affected (0.01sec)

Mysql > quit

Bye

Specify the socket file path of mysql after / /-S, which is also a way to log in to mysql. Because there are two mysql ports running on a CVM, you can only use the-S method to distinguish.

Create the db1 library and copy the data from the mysql library to it:

Mysqldump-uroot-S / tmp/mysql.sock mysql > 123.sql # backup mysql-uroot-S / tmp/mysql.sock db1

< 123.sql #恢复 设置主master 修改配置文件: vim /etc/my.cnf 在[mysqld]部分查看是否有以下内容，如果没有则添加: server-id=1 log-bin=mysql-bin //可以修改为aiker等等 除了这两行是必须的外，还有两个参数，你可以选择性的使用其中一个: #指定库的主从 #binlog-do-db=db1,db2 #忽略指定库主从，黑名单 #binlog-ignore-db=mysql binlog-do-db= #需要复制的数据库名，多个数据库名，使用逗号分隔。 binlog-ignore-db= #不需要复制的数据库库名，多个数据库名，使用逗号分隔。这两个参数其实用一个就可以 重启mysql服务： /etc/init.d/mysqld restartls /data/mysql 查看/data/mysql下，会出现以log-bin值文件: aiker.000001 设置mysql数据库的root访问密码: 设置密码：mysqladmin -uroot -S /tmp/mysql.sock password '199610' 登录：mysql -uroot -S /tmp/mysql.sock -p'199610' mysql>

Grant replication slave on *. * to 'repl'@'127.0.0.1' identified by' 123456'

/ / the repl here is the user who accesses the mysql data of the Mastermind set for slave, with a password of 123456. Here, 127.0.0.1 is the ip of slave (because the master and slave we configured are both local).

Mysql > flush privileges; # Refresh mysql > flush tables with read lock; # lock the database. No data changes are allowed at this time: mysql > unlock tables;mysql > show master status # check the status, these data are to be recorded You will use +-+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +-on slave in a moment. -+-+ | aiker.000001 | 587 | +-+ 1 row in set (0.00 sec) set slave

Modify the configuration file my.cnf:vim / usr/local/mysql_slave/my.cnf of slave first

Find the line "server-id = 1" and delete it or change it to "server-id = 2". In short, don't make this id the same as master, or you'll get an error.

In addition, from the top, the following two lines, black and white list, to see whether the main mysql is configured

Replicate-do-db=db1,db2replicate-ignore-db=db1,db2

After the modification, restart slave:

Service mysqld_slave restart

Copy the data from the db1 library on master to slave. Because both master and slave are on the same CVM, it is much easier to operate. If it is a different machine, you may need to copy it remotely. Please pay attention to this:

[root@localhost ~] # mysqldump-uroot-S / tmp/mysql.sock-pyourpassword db1 > 123.sql [root@localhost ~] # mysql-uroot-S / tmp/mysql_slave.sock-pyourpassword-e "create database db1" [root@localhost] # mysql-uroot-S / tmp/mysql_slave.sock-pyourpassword db1

< db1.sql 二行中,-e选项，它用来把mysql的命令写到shell中，这样可以方便把mysql操作写进脚本中，它的格式就是 -e "commond" 它很实用。 把数据拷贝过来后，就需要在slave上配置主从了: 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='123123',master_log_file='aiker.000001', master_log_pos=587;Query OK, 0 rows affected (0.02 sec) mysql > slave start;Query OK, 0 rows affected (0.00 sec) mysql > show slave status\ G

Unlock on the primary mysql

Mysql-uroot-S / tmp/mysql_slave.sock-p123123-e "unlock tables"

Or

Mysql > unlock tables;Query OK, 0 rows affected (0.00 sec)

3. Test master and slave

Execute the following command on master:

[root@hardman bin] # mysql-uroot-S / tmp/mysql.sock-pederew-e "use db1;select count (*) from db" +-+ | count (*) | +-+ | 2 | +-+ [root@hardman bin] # mysql-uroot-S / tmp/mysql.sock-pederew-e "use db1 Truncate table db "clear db1 table [root@hardman bin] # mysql-uroot-S / tmp/mysql.sock-pederew-e" use db1;select count (*) from db "+-+ | count (*) | +-+ | 0 | +-+ [root@hardman ~] # mysql-S / tmp/mysql_slave.sock-e" use db1 Select count (*) from db "+-+ | count (*) | +-+ | 0 | +-+

The table on slave has also been emptied. This doesn't seem obvious, so continue to delete the db table:

[root@hardman] # mysql-S / tmp/mysql_slave.sock-e "use db1;select count (*) from db" ERROR 1146 (42S02) at line 1: Table 'db1.db' doesn't exist

Master and slave configuration is very simple, but this mechanism is also very fragile, once we accidentally write data on the slave, then the master and slave will be destroyed. In addition, if you restart master, be sure to stop slave first, that is, you need to execute the slave stop command on slave, and then restart master's mysql service, otherwise it is likely to be interrupted. Of course, after the restart, you also need to turn on slavestart for slave.

What do you think of this article after reading the detailed steps of configuring MySQL Replication? If you want to know more about it, you can continue to follow our industry information section.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.