ubuntu14.04 MySQL installation to master-slave replication

1. Installation Environment:
   UbuntuServer 14.10
   Mysql-5.6.23.tar.gz
   2. Install the necessary tools
   sudo apt-get install make Bison g++ build-essential libncurses5-dev cmake
   3. Add group user settings install directory permissions
   sudo groupadd MySQL
   sudo useradd-g mysql mysql-s/bin/false #创建用户mysql并加入到mysql组, does not allow MySQL users to log in directly to the system
   sudo mkdir–p/usr/local/mysql #创建Mysql安装目录
   sudo mkdir-p/usr/local/mysql/data
   sudo mkdir-p/usr/local/mysql/log
   sudo chown-r mysql:mysql/usr/local/mysql/data
   sudo chown-r mysql:mysql/usr/local/mysql
   4. Compile and install MySQL
   4.1 Getting the source package
   Cd/usr/local/src
   Wgethttp://mirrors.sohu.com/mysql/MySQL-5.6/mysql-5.6.36.tar.gz
   4.2 Extracting MySQL Source package
   sudo tar–zxvf mysql-5.6.23.tar.gz
   5. Compiling the configuration
   CD mysql-5.6.23
   cmake-dcmake_install_prefix=/data/mysql-dsysconfdir=/etc-dmysql_unix_addr=/tmp/mysql.sock-ddefault_charset= Utf8-ddefault_collation=utf8_general_ci-dextra_charsets=all-dwith_myisam_storage_engine=1-dwith_innobase_ storage_engine=1-dwith_memory_storage_engine=1-dwith_readline=1-denabled_local_infile=1-dmysql_datadir=/data/ Mysql/data-dmysql_user=mysql-dwith_debug=0
   
   Precautions:
   When you recompile, you need to clear the old object file and cache information.
   # Make Clean
   # rm-f CMakeCache.txt
   # RM-RF/ETC/MY.CNF
   sudo make-j16 #-j Digital representation with multi-core operation
   sudo make install
   6. Related Configurations
   6.1 Configuring Boot Start
   sudo chmod +w/usr/local/mysql
   sudo cp./support-files/my-default.cnf/etc/my.cnf
   sudo cp./support-files/mysql.server/etc/init.d/mysqld
   sudo chmod 755/etc/init.d/mysqld
   6.2 Common Command Soft links, setting environment variables
   Ln-s/usr/local/mysql/lib/libmysqlclient.so.18/usr/lib/libmysqlclient.so.18
   Add/usr/local/mysql/bin to Path-->vim/etc/profile.d/mysql.sh
3. Export Mysql_home=/data/mysql

5. Export path= $MYSQL _home/bin: $PATH

7. 6.3 Initializing Database
   sudo/usr/local/mysql/scripts/mysql_install_db--defaults-file=/etc/my.cnf--basedir=/usr/local/ MySQL--datadir=/usr/local/mysql/data--user=mysql
   7. Start the MySQL service and try it.
   sudo/etc/init.d/mysqld start
   8. Create the root user's password after successful startup
   Mysqladmin -U root password or so mysql_secure_installation---->mysqladmin-uroot-p
9. 9. Use after success
   Mysql-uroot-p
   Comments:
   I put the installation path in the/data/mysql/MySQL, also is the default path, the data is placed under/usr/local/mysql/data/, sock file put to/usr/local/mysql/ Mysqld.sock
   Some of the parameters are as follows: choose as needed.
   Cmake_install_prefix: Specifies the installation directory of the MySQL program, default/usr/local/mysql
   Default_charset: Specifies the server default character set, default Latin1
   Default_collation: Specifies the default proofing rules for the server, default Latin1_general_ci
   Enabled_local_infile: Specifies whether to allow load DATA INFILE to be executed locally, by default off
   With_comment: Specifying compilation Notes Information
   With_xxx_storage_engine: Specifies a storage engine that is statically compiled to MySQL, and the Myisam,merge,member and CSV four engines are compiled to the server by default and do not require special designation.
   Without_xxx_storage_engine: Specifying a storage engine that does not compile
   Sysconfdir: Initialize parameter file directory
   Mysql_datadir: Data Files directory
   Mysql_tcp_port: Service port number, default 3306
   Mysql_unix_addr:socket file path, default/tmp/mysql.sock

Master-Slave synchronization:

On the slave server, perform the following steps to modify the configuration file: server-id=2

Establish an account on the primary server and authorize slave:

>grant REPLICATION SLAVE on * * to ' mysync ' @ ' percent ' identified by ' q123456 ';

6, log on to the master server MySQL, query the status of master
Mysql>show Master status;
+------------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+------------------+----------+--------------+------------------+
|      mysql-bin.000004 |              308 |                  | |
+------------------+----------+--------------+------------------+
1 row in Set (0.00 sec)
Note: Do not operate the master server MySQL again after performing this step to prevent the change of the primary server state value

7. Configure the slave from the server:
Mysql>change Master to master_host= ' 192.168.145.222 ', master_user= ' Mysync ', master_password= ' q123456 ',
Master_log_file= ' mysql-bin.000004 ', master_log_pos=308; Be careful not to disconnect, there are no single quotes around 308 digits.

Mysql>start slave; To start the Copy from Server feature

8. Check the status of the replication function from the server:

Mysql> Show Slave Status\g

1. Row ***************************

Slave_io_state:waiting for Master to send event
master_host:192.168.2.222//Primary server address
Master_user:mysync//Authorization account name, try to avoid using root
master_port:3306//Database port, some versions do not have this line
Connect_retry:60
master_log_file:mysql-bin.000004
read_master_log_pos:600//#同步读取二进制日志的位置, greater than or equal to Exec_master_log_pos
relay_log_file:ddte-relay-bin.000003
relay_log_pos:251
relay_master_log_file:mysql-bin.000004
Slave_io_running:yes//This status must be Yes
Slave_sql_running:yes//This status must be Yes
......

Note: The slave_io and slave_sql processes must function normally, that is, the Yes state, otherwise it is an error state (e.g., one of the No is an error).

The above operation process, the master and slave server configuration is complete.

MySQL sync failure: "Slave_sql_running:no" two solutionsreprinted March 30, 2016 13:41:12

Enter the slave server and run:

Mysql> Show Slave Status\g

.......
relay_log_file:localhost-relay-bin.000535
relay_log_pos:21795072
relay_master_log_file:localhost-bin.000094
Slave_io_running:yes
Slave_sql_running:no
replicate_do_db:
replicate_ignore_db:
......

Workaround I.

Slave_sql_running:no
1. The program may have been written on the slave

2. It is also possible that the transaction rollback is caused by the slave machine being reset.

This is typically caused by a transaction rollback:
Workaround:
mysql> stop Slave;
Mysql> set GLOBAL sql_slave_skip_counter=1;
mysql> start slave;

Solution II,

First stop the slave service: Slave stop
To view host status on the primary server:
Record the value corresponding to file and position

Enter Master

Mysql> Show master status;
+----------------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+----------------------+----------+--------------+------------------+
| localhost-bin.000094 |              33622483 |                  | |
+----------------------+----------+--------------+------------------+
1 row in Set (0.00 sec)

Then perform a manual synchronization on the slave server:

mysql> change master to 
> master_host=‘master_ip‘,
> master_user=‘user‘, 
> master_password=‘pwd‘, 
> master_port=3306, 
> master_log_file=localhost-bin.000094‘, 
> master_log_pos=33622483 ;
1 row in set (0.00 sec)
mysql> start slave ;
1 row in set (0.00 sec)

Mysql> Show Slave Status\g
1. Row ***************************
........
master_log_file:localhost-bin.000094
read_master_log_pos:33768775
relay_log_file:localhost-relay-bin.000537
relay_log_pos:1094034
relay_master_log_file:localhost-bin.000094
Slave_io_running:yes
Slave_sql_running:yes
replicate_do_db:

Manual sync needs to stop Master's write operation!

/data/mysql:mysql installation directory

/data/mysql/data:mysql Data Catalog

/data/mysql/log:mysql Log Directory

/etc/:mysql Configuration file Directory

/tmp/: Block Directory

ubuntu14.04 MySQL installation to master-slave replication