MySQL Replication is a master-slave copy

MySQL replication is mainly used for MySQL backup or read/write separation. Do the preparation before configuring, configure two MySQL servers, or configure two ports on a single server.
Process:

A-->change Data-->bin_log-->transfer-->b-->repl_log-->change Data

One, built a MySQL, run is 3306 port.

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

2. Decompression

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

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

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

4. Build MySQL Users

useradd?-s?/sbin/nologin?mysql

5. Initializing the database

6. Copy the configuration file
cp support-files/my-large.cnf ? /etc/my.cnfCopy 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 the startup script

vim /etc/init.d/mysqld

Need to modify the place there is "datadir=/data/mysql”
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?basedir=/usr/local/mysql
9. Add the startup script to the system service item and set the boot start, start MySQL

chkconfig --add mysqldchkconfig  mysqld on 或service  mysqld  start或/etc/init.d/mysqld  start

The command to check if Mysqld is started is:ps aux |grep mysqld
Second, build a 3307-port MySQL:

[[email protected]?~]#? mkdir  /data/mysql_slave[[email protected]?~]# chown  -R  mt=ysql:mysql  /data/mysql_slave[[email protected]?~]#?cd?/usr/local/[[email protected]?local]#?cp?-r?mysql?mysql_slave[[email protected]?local]#?cd?mysql2[[email protected] mysql_slave]#./scripts/mysql_install_db   --user=mysql? --datadir=/data/mysql_slave [[email protected]_slave]#?cp?/etc/my.cnf? .[[email protected]?mysql_slave]#?vim?my.cnf

【mysqld】?改为:port = 3307 ??改为:socket = /tmp/mysql_slave.sock在这一行的下面再加一行:?datadir=?/data/mysql_slave?

Once saved, you can start it:

[[email protected] mysql_slave]#?cd bin/[[email protected] bin]# ./mysqld_safe --defaults-file=../my.cnf --user=mysql &重启：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 booting, you need to add the boot command to/etc/rc.local.
If you want the script to start like mysqld, then:

[[email protected] mysql_slave]#?cd /etc/init.d/[[email protected] init.d]# cp mysqld ?mysqldslavevim mysqldslave改datadir=/data/mysql” ? ?为 ? datadir=/data/mysql_slave?? basedir=/usr/local/mysql 为 ? basedir=/usr/local/mysql_slave改conf=/etc/my.cnf ? ? ? ? ? ?为 ? conf=$basedir/my.cnf

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

cp -r mysql mysql_slave

Same modification

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

The Conf path behind

conf=$basedir/my.cnf

However, you cannot start with/etc/init.d/mysqlslave start
You can use the service Mysqlslave start

[[email protected] bin]# chkconfig --add mysqldslave [[email protected] bin]# chkconfig mysqldslave on[[email protected] bin]# chkconfig --add mysqld[[email protected] 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

Third, create the test database on the master server:
Third, create the test database on the master server:
Log in to two MySQL

mysql -h127.0.0.1 -P3306    #通过主机端口登陆mysql -h127.0.0.1 -P3307    mysql -S /tmp/mysql.sock    #通过sock登陆mysql -S /tmp/mysql_slave.sock  mysql -uroot  -p密码  #通过密码登陆

Login mysql:/usr/local/mysql/bin/mysql-uroot-p199610
? ? ? ? Or mysql-s?/tmp/mysql.sock?
Login mysql_slave:mysql-s/tmp/mysql_slave.sock? or mysql-h127.0.0.1-p3307 (no secret)
?
The 3306 port of MySQL as the Master (master), and 3307 of MySQL as the slave (slave)
To make the experiment more like a production environment, first create a library on master DB1
[[Email protected]?bin]#?mysql?-uroot?-s?/tmp/mysql.sock-p199610

mysql>?create?database?db1;
Query? Ok,?1?row?affected? (0.01?SEC)

Mysql>?quit
Bye
-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 the DB1 library and copy the data from the MySQL library to it:

mysqldump?-uroot?-S?/tmp/mysql.sock? mysql?>?123.sql ? ? #备份?mysql?-uroot?-S?/tmp/mysql.sock?db1?<?123.sql ? ? #恢复

1. Setting the Master Master
   To modify a configuration file:

   vim?/etc/my.cnf

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

   server-id=1

   Log-bin=mysql-bin//Can be modified to aiker, etc.
   In addition to these two lines are necessary, there are two parameters, you can choose to use one of them:
   #指定库的主从

   #binlog-do-db=db1,db2

   #忽略指定库主从, blacklist

   #binlog-ignore-db=mysql

   binlog-do-db=#需要复制的数据库名, multiple database names, separated by commas.
   binlog-ignore-db= #不需要复制的数据库库名, multiple database names, separated by commas. These two parameters can actually be used in one
   To restart the MySQL service:

   /etc/init.d/mysqld restart

   ls ?/data/mysql

   Under View/data/mysql, an Log-bin value file appears:

   aiker.000001??

   ?
   To set the root access password for the MySQL database:
   Set Password:mysqladmin?-uroot?-S?/tmp/mysql.sock?password?‘199610‘
   Login: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 master-side MySQL data for the slave end, with a password of 123456, where the 127.0.0.1 is the slave IP (as we configured both master and slave are native).

   mysql>?flush privileges;     ? ? ? #刷新??mysql>flush tables with read lock;   #锁定数据库，此时不允许更改任何数据mysql>unlock tables;mysql> show master status;  #查看状态，这些数据是要记录的，一会要在slave端用到+--------------+----------+--------------+------------------+| File         | Position | Binlog_Do_DB | Binlog_Ignore_DB |+--------------+----------+--------------+------------------+| aiker.000001 |      587 |              |                  |+--------------+----------+--------------+------------------+1 row in set (0.00 sec)

3. Set slave
   First modify the slave configuration file my.cnf:

   vim?/usr/local/mysql_slave/my.cnf

   Find "Server-id = 1" This line, delete or change to "Server-id = 2" In short, you can not let this ID and master, otherwise it will be an error.
   In addition, from the top, such as the next two lines, black and white list, see Master MySQL has no configuration

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

After the change, restart slave:

service mysqld_slave restart

Copy the data from the DB1 library on master to slave, because both master and slave are on one server, so it's a lot easier to operate, and if it's a different machine, it might require remote copy, and I hope you notice this:

[[email protected]?~]#?mysqldump?-uroot?-S?/tmp/mysql.sock?-pyourpassword db1?>?123.sql[[email protected]?~]#?mysql?-uroot?-S?/tmp/mysql_slave.sock?-pyourpassword?-e?"create?database?db1"[[email protected]?~]#?mysql?-uroot?-S?/tmp/mysql_slave.sock?-pyourpassword?db1?<?db1.sql

In two lines, the-e option, which is used to write MySQL commands to the shell, so that the MySQL operation can be easily written into the script, its format is?-e? " Commond "? It's very practical.
After copying the data, you need to configure the master/slave on the 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 main 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:

[[email protected] bin]# mysql -uroot -S /tmp/mysql.sock -pederew -e "use db1;select count(*) from db"

+----------+| count(*) |+----------+|        2 |+----------+[[email protected] bin]# mysql -uroot -S /tmp/mysql.sock -pederew -e "use db1;truncate table db" 清空db1的表[[email protected] bin]# mysql -uroot -S /tmp/mysql.sock -pederew -e "use db1;select count(*) from db"   +----------+| count(*) |+----------+|        0 |+----------+[[email protected] ~]# mysql -S /tmp/mysql_slave.sock -e "use db1;select count(*) from db"+----------+| count(*) |+----------+|        0 |+----------+

The table on the slave was also emptied. This may not seem obvious, so try to keep the DB table removed:

[[email protected] ~]# 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

The master-slave configuration is very simple, but this mechanism is also very fragile, once we accidentally write the data on the slave, then the master and slave is destroyed. In addition, if you restart Master, be sure to stop the slave first, that is, you need to slave up the slave Stop command, and then to restart the master MySQL service, otherwise it will likely be interrupted. Of course, after the restart, you also need to slave to open slavestart.

MySQL Replication is a master-slave copy