[Linux]-mysql Master-slave replication

Types of replication supported by MySQL:

1) statement-based replication. Executes the SQL statement on the primary server, executing the same statement from the server. MySQL default to use statement-based replication, high efficiency;

2) row-based replication. Copy the changes to the past, rather than executing the commands from the server;

3) mixed-type replication. By default, statement-based replication is used, and when a statement cannot be accurately replicated, row-based replication occurs;

The principle of MySQL master-slave replication:

1) in master, after each thing update data is completed, write to the binary log;

2) The slave I/O thread monitors the binary log of master, and once the log changes, it reads and writes the changed portions to the slave trunk log.

3) The SQL thread in the slave monitors the relay log, and once the change is read, replay (re-execute the SQL statement) to synchronize the MySQL data.

Primary MySQL server (master)------configured from MySQL server (slave)

Mount  /dev/cdrom  /mnt/    //mounting discs using Yum

1, install Yum, put the required installation package in the server, yum will automatically install dependencies

rm -rf/etc/yumrepos.d/*[[email protected] ~]# Vim/etc/yum.repos.d/yum.repo

[yum]name=Yuminstallbaseurl=file:///mnt/ enabled=1gpgcheck=0

2. Install MySQL server

#!/bin/bash# Installing the MySQL service # initializes the current path Pwd_dir=`pwd' #######################[initialize variable]####################### #mysql源码包的名称mysql_package_name='mysql-5.5.22.tar.gz'#cmake源码包的名称cmake_package_name='cmake-2.8.6.tar.gz'#mysql安装路径mysql_install='/usr/local/mysql'#mysql解压出源码包目录的名称mysql_package_dir='mysql-5.5.22'#cmake解压出源码包目录的名称cmake_pachage_dir='cmake-2.8.6'#源码包解压存放路径code_package_dir='/usr/src/'#依赖包的文件名称subjoin_package_file='ncurses-devel-5.7-3.20090208.el6.x86_64.rpm'###########################[end]############################ #卸载rpm方式的安装的mysql服务rpm-e mysql-server--nodeps &>/dev/NULL#检查依赖包rpm-Q ncurses-devel &>/dev/NULLif[$?-ne0 ] Thenrpm-ih $subjoin _package_fiel--nodepsfi#编译安装cmakeTarZXVF $cmake _package_name-c $code _package_dir &>/dev/NULLCD $code _package_dir$cmake_pachage_dir./configure &>/dev/NULLGmake&>/dev/NULLGmakeInstall&>/dev/NULLCD $pwd _dir# compile and install MSYQLTarZXF $mysql _package_name-c $code _package_dir &>/dev/NULLCD $code _package_dir$mysql_package_dircmake-dcmake_install_prefix= $mysql _install-dsysconfdir=/etc/-ddefault_charset=utf8-ddefault_collation=utf8_general_ Ci-dwhit_charsets=all &>/dev/NULL Make&>/dev/NULL Make Install&>/dev/NULL#配置msyqluserdel MySQL&>/dev/NULLUseradd-m-s/sbin/nologin MySQL &>/dev/NULLChown-R mysql:mysql $mysql _install &>/dev/NULLCP-F ${mysql_install}/support-files/my-medium.cnf/etc/my.cnfLN-S ${mysql_install}/bin/*/USR/LOCAL/BIN/CP ${mysql_install}/support-files/mysql.server/etc/rc.d/init.d/mysqldchkconfig--add Mysqldchkconfig mysqld on# Initialize mysql$mysql_install/scripts/mysql_install_db--user=mysql--basedir= $mysql _install-- datadir=${mysql_install}/data/&>/dev/nullservice mysqld start &>/dev/nullecho ' MySQL Install Successful '

MySQL Script3. Configure the primary MySQL server (master)

Modify the MySQL configuration file

[[email protected] ~]# vim/etc/// omit partial configuration content   for replicationlog- Bin=master-bin         // modify server-ID       1           // master server, the smaller the number, the higher the level log-slave-updates=true   // Modify
[[Email protected] ~]# service mysqld Restart  //restart Services

The primary server logs on to MySQL to synchronize permissions from the server

[Email protected] ~]# mysql-u root-p
mysql> grant replication Slave on *.'Myslave'@'192.168.1.%'Identified by'123.com';mysql> flush Privileges; Force Refresh
Mysql> Show master status;
+-------------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+-------------------+----------+--------------+------------------+
|      master-bin.000001 |              338 |                  | |
+-------------------+----------+--------------+------------------+
1 row in Set (0.00 sec)

Where the file column displays the log name, the position column shows the offset, which is required after the configuration from the server.

Slave should make new updates on master from this point

Configure MySQL slave server (slave)

server-ID       2       //modified to be less than master MySQL, not as relay-log=relay-log-bin   //Add Relay -log-index=slave-relay-bin.index   //Add
[[Email protected] ~]# service mysqld Restart   //restart Services

Log in to MySQL, configure synchronization

Change the Master_log_file and Master_log_pos parameters in the following command by the file information and position location number of the master server;

[Email protected] ~]# mysql-u root-pmysql> Change master to master_host='192.168.1.9 ',
master_user=' myslave', master_password='123.com',
master_log_file='master-bin.000001',
master_log_pos=338;
mysql> start slave;    Start real-time synchronization

Mysql>show Slave status\g; To view the status information for a synchronization***************************1. Row ***************************slave_io_state:connecting to master master_host:192.168.1.9master_user:myslave Master_port:3306Connect_retry: -Master_log_file:master-bin.000001Read_master_log_pos:338Relay_log_file:relay-log-bin.000001Relay_log_pos:4Relay_master_log_file:master-bin.000001slave_io_running:connecting Slave_sql_running:yes Replicate_do_db:re Plicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicat E_wild_ignore_table:last_errno:0Last_error:skip_counter:0Exec_master_log_pos:338Relay_log_space:107until_condition:none Until_log_file:until_log_pos:0Master_ssl_allowed:no Master_SSL_CA_File:Master_SSL_CA_Path:Master_SS L_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master:NULLMaster_SSL_Verify _server_cert:no Last_io_errno:2003Last_io_error:error connecting to master'[Email protected]:3306'-retry- Time: -Retries:86400Last_sql_errno:0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id:0

Verify

Create a library on the primary server and then swipe from the server to see the libraries we created

MASTER-BIN.000001 is a binary file that contains the SQL statement that we created to write the library

We can use the Mysqlbinlog command to view the MySQL binary log file, followed by the path

[Linux]-mysql Master-slave replication