MySQL master-slave introduction MySQL master-slave is also calledReplication, AB replication。 Simply speaking is a and b two machines from the back, write the data on a, the other B will follow the writing data, both data real-time synchronization of MySQL master-slave is based on Binlog, the Lord must open Binlog to carry out master and slave. The master-slave process is roughly 3 steps 1The change operation is recorded in Binlog.2)from synchronizing the main binlog event (SQL statement) to the native and recording in Relaylog3)execute sequentially from the SQL statements inside the RelaylogThe Lord has a log dump thread that is used to pass the binlog from the I/O thread, where I/O threads are used to synchronize the main binlog and generate Relaylog, and another SQL thread is used to manipulate the SQL statements inside the Relaylog: the main Server 192.168.4.81 from server: 192.168.4.82 192.168.4.81, 192.168.4.82: Installing MySQLMySQL InstallationYum install-y perl-module-install.noarchcd/usr/local/srcwget http://mirrors.sohu.com/mysql/MySQL-5.6/ Mysql-5.6.35-linux-glibc2.5-x86_64.tar.gztar ZXVF MYSQL-5.6.35-LINUX-GLIBC2.5-X86_64.TAR.GZMV Mysql-5.6.35-linux-glibc2.5-x86_64/usr/local/mysqlcd/usr/local/mysqluseradd-s/sbin/nologin MySQL mkdir-p/data/ MySQL Chown-r mysql:mysql/data/mysql./scripts/mysql_install_db--user=mysql--DATADIR=/DATA/MYSQLCP support-files/ MY-DEFAULT.CNF/ETC/MY.CNF CP Support-files/mysql.server/etc/init.d/mysqldchmod 755/etc/init.d/mysqld vim/etc/ Init.d/mysqld define Basedir and DataDir basedir=/usr/local/mysqldatadir=/data/mysql/etc/init.d/mysqld start Chkconfig--add Mysqldchkconfig mysqld on and add the path/usr/local/mysql/bin to the environment variable/etc/profile source/etc/profileLogin Main:CREATE database db1;[Email protected] mysql]# mysql-urootmysql> CREATE database db1;mysql> CREATE TABLE T1 (c1 int); Query OK, 0 rows affected (0.03 sec) mysql> CREATE TABLE t2 (c1 int); Query OK, 0 rows affected (0.06 sec) mysql> CREATE TABLE t3 (c1 int); Query OK, 0 rows affected (0.01 sec) Backup Database db1 to Db1.sql [email protected] mysql]#mysqldump-uroot db1 >/tmp/db1.sql and transfer the SQL file to the server from 82scp/tmp/db1.sql [Email protected]:/tmpedit the main mysql configuration fileDataDir =/data/mysql# port = ... ..server_id = Bayilog-bin=weifeng1socket =/tmp/mysql.sockService mysqld Restart re-MySQL services log in to master:mysql>grant replication Slave on * * to ' repl ' @ ' 192.168.4.82 ' identified by ' 123456 ';Query OK, 0 rows Affected (0.00 sec) Create User rights replication, user name Repl, password 123456mysql>flush Privileges;Refresh permissions Query OK, 0 rows Affected (0.00 sec) mysql>flush tables with read lock;Query OK, 0 rows Affected (0.00 sec) Table read lock dead mysql>show master status;View Master Status +----------------+----------+--------------+------------------+-------------------+| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |+----------------+----------+--------------+------------------+-------------------+| weifeng.000002 | 408| DB1,DB2 | MySQL | |+----------------+----------+--------------+------------------+-------------------+1 row in Set (0.00 sec) login from MySQL : Modify the configuration file:DataDir =/data/mysql# port = ... ..server_id = thesocket =/tmp/mysql.sockRestart MySQL database service mysqld Restart login from database: mysql-uroot CREATE DATABASE db1;mysql>. Restore Database db1 [[email protected] mysql]#mysql-uroot DB1 </tmp/db1.sqlLogin from: mysql>stop slave;Query OK, 0 rows affected, 1 Warning (0.00 sec) mysql>Change master to master_host= ' 192.168.4.81 ', master_port=3306,master_user= ' repl ', master_password= ' 123456 ', Master_log_file= ' weifeng.000002 ', master_log_pos=408;Query OK, 0 rows affected, 2 warnings (0.02 sec) mysql>start slave;Query OK, 0 rows Affected (0.00 sec) mysql> Show slave status\g;*************************** 1. Row *************************** slave_io_state:waiting for Master to send event Master_Host:192.168.4.81 Master_User:repl master_port: 3306 connect_ Retry:60 master_log_file: weifeng.000002 read_master_log_pos:408 relay_log_file:localhost-relay-bin.000002 Relay_Log_Pos:281 Relay_Master_Log_File:weifeng.000002 Slave_io_running:yesSlave_sql_running:yesIf running 2 is yes, the configuration is successful .Test master-Slave mysql>unlock tables;mysql>use db1;mysql>show tables;mysql>drop table help_category; View from above without this table Mysql>drop table proc; From the view above without this table mysql>show CREATE TABLE user\g:mysql> creates a Weifeng table from the top view can see this table Mysql>drop database db1;mysql> show databases; There's no such library from the top.
MySQL Configuration master-Slave
