MySQL Master-Slave synchronization

Build MySQL master-slave replication installation Environment:

Two CentOS 7 virtual machines

First, set up time synchronization environment, on the master node to build time synchronization Server master server Settings 1, install NTP

[[email protected]~]#yum install ntp -y

2. Configure NTP

[[email protected]~]#vim /etc/ntp.conf  //添加下面两行server 127.127.200.0     //本地时钟源fudge  127.127.200.0 stratum 8  //设置时间层级为8（限制在15层以内）

Note: Here 127.127.200.0 one by one corresponds to the primary server of 192.168.200.0, if the primary server is 192.168.100.0, the local clock source is 127.127.100.0, the first two parts are always the same

[[email protected]~]#systemctl start ntpd.service[[email protected]~]#systemctl stop firewalld.service[[email protected]~]#setenforce 0

3. Node Server settings:

[[email protected]~]#yum install ntp  ntpdate -y[[email protected]~]#systemctl start ntpd.service[[email protected]~]#systemctl stop firewalld.service[[email protected]~]#systemctl stop firewalld.service[[email protected]~]#setenforce 0[[email protected]~]#/usr/sbin/ntpdate master_server IP   //进行时间同步

Install MySQL database, install 1 on master and Slavel, install Environment pack:

[[email protected]~]#yum -y install ncurses ncurses-devel bison cmake gcc  gcc-c++

2. Compile and install MySQL:

[[email protected]~] #useradd-S/sbin/nologin mysql//create MySQL user [[email protected]~] #mkdir/abc[[email  protected]~] #mount. CIFS//vmnet8_ip/abc[[email protected]~] #cd/abc[[email protected] ABC] #tar ZXVF mysql-5.7.17.tar.gz-c/opt/[[email protected] ABC] #tar ZXVF boost_1_59_0.tar.gz-c/usr/local/[[email  PROTECTED] ABC] #cd/usr/local [[email protected] local] #mv boost_1_59_0 boost//easy identification [[email protected] Local ] #cd opt/mysql-5.7.17/[[email protected] mysql-5.7.17] #cmake-dcmake_install_prefix=/usr/local/mysql \//Specify Directory- Dmysql_unix_addr=/usr/local/mysql/mysql.sock \//connection file-dsysconfdir=/etc \//config file directory-dsystemd_pid_di R=/USR/LOCAL/MYSQL \//process file-ddefault_charset=utf8 \//Character Set-ddefault_collation=utf8_general_ci- Dwith_innobase_storage_engine=1-dwith_archive_storage_engine=1-dwith_blackhole_storage_engine=1-dwith_ Perfschema_storage_engine=1-dmysql_datadir=/usr/local/mysql/data \//Database-dwith_boost=/usr/local/boost \//support C + + library-dwith_systemd=1 

Note: If there is an error in the process of CMake, when the error is resolved, you need to remove the CMakeCache.txt file from the source directory, and then cmake again, otherwise the errors are still

[[email protected] mysql-5.7.17] #make && make install[[email protected] mysql-5.7.17] #chown-R Mysql.mysql/usr/local/mysql[[email protected] mysql-5.7.17] #vim/etc/my.cnf[client]port = 3306default-character-set=utf8socket =/usr/local/mysql/mysql.sock[mysql]port = 3306default-character-set= Utf8socket =/usr/local/mysql/mysql.sock[mysqld]user = Mysqlbasedir =/usr/local/mysqldatadir =/usr/local/mysql/ dataport = 3306character_set_server=utf8pid-file =/usr/local/mysql/mysqld.pidsocket =/usr/local/mysql/ Mysql.sockserver-id = 1sql_mode=no_engine_substitution,strict_trans_tables,no_auto_create_user,no_auto_value_on_ zero,no_zero_in_date,no_zero_date,error_for_division_by_zero,pipes_as_concat,ansi_quotes! [] (http://i2.51cto.com/images/blog/201807/10/60aa994307427a5fc19112d1e2ed54d6.png?x-oss-process=image/ watermark,size_16,text_qduxq1rp5y2a5a6i,color_ffffff,t_100,g_se,x_10,y_10,shadow_90,type_zmfuz3pozw5nagvpdgk=) ##### 3, optimization adjustment [[email protected] mysql-5.7.17] #chown MYSQL.MYSQL/ETC/MY.CNF//Change owner, group permissions [[email protected] mysql-5.7.17] #echo ' path=/usr/local/mysql/bin:/usr/ Local/mysql/lib: $PATH ' >>/etc/profile[[email protected] mysql-5.7.17] #echo ' export PATH ' >>/etc/ Profile[[email protected] mysql-5.7.17] #source/etc/profile##### 4, initialize database [[email protected] mysql-5.7.17 ] #cd/usr/local/mysql/[[email protected] MySQL] #bin/mysqld--initialize-insecure \//Generate initialized password, empty, need to set--user= later MySQL--basedir=/usr/local/mysql--datadir=/usr/local/mysql/data[[email protected] MySQL #cp usr/lib/systemd/ system/mysqld.service/usr/lib/systemd/system///Easy system identification management [[email protected] MySQL] #systemctl daemon-reload// Refresh Recognition Command ##### 5, restart MySQL service [[email protected] mysql]# systemctl restart mysqld [[email protected] mysql]# NETSTAT-ANPT | grep 3306//See if the port is on [[email protected] MySQL] #systemctl enable mysqld [[email protected] MySQL] #mysqladmin- U root-p Password "abc123"//to the root account set password for abc123 prompt input is the original password [[email protected] mysql] #mysql-u root-p 

The display indicates that the database was successfully built

Third, configure MySQL Master master server 1) in the/ETC/MY.CNF to modify or add the following, under [mysqld] Configuration

 server-id     =11     //主从服务器id不能相同 log-bin=master-bin    //改为master log-slave-updates=true   //同步从服务器

2) Restart the MySQL service, reload the modified configuration file

 [[email protected] ~]#systemctl mysqld.service

3) Log in to MySQL program and give authorization from server

 [[email protected] ~]#mysql -u root -p

Iv. Configuring the Slave server

1) Modify or add the following content in/ETC/MY.CNF

 [[email protected] ~]#vim /etc/my.cnf server-id       = 22relay-log=relay-log-bin                         //从主服务器上同步日志文件记录到本地//relay-log-index=slave-relay-bin.index           //定义relay-log的位置和名称//

Note here that Server-id cannot be the same as the primary server

2) Restart MySQL service

 [[email protected] ~]#systemctl mysqld.service

3) Log in to MySQL, configure synchronization

Changing parameters for Maste_log_file and Master_log-pos based on the primary server
Mysal>
Change Master to master_host= ' master_server_ip ', master_user= ' myslave ', master_password= ' 123456 ', master_log_file= ' Master-bin.000001 ', master_log_pos=604;

4) Start synchronization

Mysql>start Salve;

5) Check the slave status to make sure the following two values are Yes

6) Verify master-slave copy effect

MySQL Master-Slave synchronization