Shulou(Shulou.com)06/01 Report--

The following mainly brings you the detailed steps of Mysql master-slave configuration. I hope these contents can bring you practical use, which is also the main purpose of this article that I edit the detailed steps of Mysql master-slave configuration. All right, don't talk too much nonsense, let's just read the following.

Environment:

System version: centos6.6

Mysql version: mysql 5.6.11

Ip status 10.0.2.87

Master 10.0.2.111 from

1. Data backup

On 10.0.2.87

First stop the service, make the database stop the data update, and then back up the database

Mysqldump-R-uroot-p nc_onldb > / root/nc_onldb_ `date +% Y% m% d`.sqlmysqldump-R-uroot-p rxrc > / root/rxrc_ `date +% Y% m% d`.sql

If the data is large, you can consider copying the library file directly

two。 Install mysql 5.6

The mysql version of the slave library is not consistent with the master library. Reinstall.

First, stop the original mysql:

/ etc/init.d/mysqld stop

1. Copy the mysql installation package from 10.0.2.87

Cd / usr/local/src/scp root@10.0.2.87:/home/nc_onldb/mysql-5.6.11.tar.gz. /

two。 Decompression

Tar xzf mysql-5.6.11.tar.gzcd mysql-5.6.11

3. Install cmake

Yum install cmake

4. Install mysql

Groupadd dbuseradd nc_onldb-g dbcmake\-DCMAKE_INSTALL_PREFIX=/usr/local/mysql\-DMYSQL_UNIX_ADDR=/var/run/mysqld/mysql.sock\-DDEFAULT_CHARSET=utf8\-DDEFAULT_COLLATION=utf8_general_ci\-DWITH_MYISAM_STORAGE_ENGINE=1\-DWITH_INNOBASE_STORAGE_ENGINE=1\-DWITH_ARCHIVE_STORAGE_ENGINE=1\-DWITH_BLACKHOLE_STORAGE_ENGINE=1\-DWITH_MEMORY_STORAGE_ENGINE=1\-DWITH_READLINE=1\-DENABLED_LOCAL_INFILE=1\-DMYSQL_DATADIR=/home/nc_onldb / DATA\-DMYSQL_USER=nc_onldb\-DMYSQL_TCP_PORT=3306

Errors that may occur:

1.-- Could NOT find Curses (missing: CURSES_LIBRARY CURSES_INCLUDE_PATH)

CMake Error at cmake/readline.cmake:82 (MESSAGE):

Curses library not found. Please install appropriate package

Remove CMakeCache.txt and rerun cmake.On Debian/Ubuntu, package name is libncurses5-dev, on Redhat and derivates it is ncurses-devel.

= install ncurses-devel, then delete CMakeCache.txt and recompile

Yum install ncurses-develrm-f CMakeCache.txt

2. CMake Error: CMAKE_CXX_COMPILER not set, after EnableLanguage

CMake Error: Internal CMake error, TryCompile configure of cmake failed

-- Performing Test HAVE_PEERCRED-Failed

= missing gcc-related packages, then delete CMakeCache.txt and recompile

Yum install gcc gcc-c++rm-f CMakeCache.txtmake & & make install

5. Initialize the database

/ usr/local/mysql/scripts/mysql_install_db-user=nc_onldb-basedir=/usr/local/mysql-datadir=/home/nc_onldb/DATA-pid-file=/var/run/mysqld/mysqld.pid

Errors may occur during initialization:

/ usr/local/mysql/bin/mysqld: File'. / mysql-bin.index' not found (Errcode: 13-Permission denied)

2016-10-19 15:06:48 30553 [ERROR] Aborting

2016-10-19 15:06:48 30553 [Note] Binlog end

2016-10-19 15:06:48 30553 [Note] / usr/local/mysql/bin/mysqld: Shutdown complete

Stop the original mysql service before initializing it.

6. Replication service startup script

Due to the older version of the mysql startup script, you should pay attention to the distinction when copying to / etc/init.d

Cp / usr/local/mysql/support-files/mysql.server / etc/init.d/mysql

7. Modify the configuration file

Mv / etc/my.cnf / etc/my.cnf.bakvi / usr/local/mysql/my.cnf [mysqld] sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES datadir=/home/nc_onldb/DATAsocket=/var/run/mysqld/mysql.sockuser=nc_onldb# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0 [mysqld_safe] log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid

Start mysql

/ etc/init.d/mysql start

The startup process may report an error:

Starting MySQL.. ERROR! The server quit without updating PID file (/ home/nc_onldb/DATA/xxx.pid).

= the reason may be the permissions of the configured pid directory. This directory is the same as the original mysql pid directory, but the permissions are the users of the original mysql. Change the user and group of / var/run/mysqld to nc_onldb.

Chown-R nc_onldb:nc_onldb / var/run/mysqld

3. Set mysql master-slave

1. Configuration Master (10.0.2.87)

Vi / usr/local/mysql/my.cnf adds the following: server-id=87log-bin=mysql-bin

Restart mysql

/ etc/init.d/mysql restart

Set permissions

/ usr/local/mysql/bin/mysql-uroot > grant replication slave on *. * to 'repl'@'10.0.2.111' identified by' aabbcc'; > flush privileges; > flush tables with read lock; # Database with read Lock > show master status; # check the status of the main database and remember that it will be used when configuring.

two。 Configuration from (10.0.2.111)

Vi / usr/local/mysql/my.cnf adds the following: server-id=111

Restart mysql

/ etc/init.d/mysql restart/usr/local/mysql/bin/mysql-uroot > stop slave; > change master to master_host='10.0.2.87', master_port=3306, master_user='repl', master_password='aabbcc', master_log_file='mysql-bin.xxxx', master_log_pos=xxxx; > start slave

On 10.0.2.87:

Unlock tables

Then look at the state of mysql from above.

Show slave status\ G (\ G means displayed as a column)

Update the master-slave relationship (bin-log changes):

From the top:

> stop slave; > reset slave; > start slave

Delete the master-slave relationship:

From the top:

> stop slave; > reset slave all

4. Mysql cleans up bin-log

(1) automatic cleanup method: edit my.cnf

Expire_logs_days = 7 / / indicates that the log is kept for 7 days, and if it exceeds 7 days, the log is set to expire # / usr/local/mysql/bin/mysql-uroot > show binary logs; > show variables like'% log%'; > set global expire_logs_days = 7

(2) manual cleaning

If there is no master and slave, you can reset the database log with the following command:

> reset master

If there is a master-slave replication, clean it up with the purge command:

/ usr/local/mysql/bin/mysql-uroot > purge master logs to 'mysql-bin.010'; / / clear the previous mysql-bin.010 logs (not including 010) > purge master logs before' 2016-02-28 13 purge 0000; / / clear the logs before 2016-02-28 13:00:00 > purge master logs before date_sub (now (), interval 3 day); / / clear the bin logs from 3 days ago

Note that do not easily delete the binlog manually, which will cause the binlog.index to mismatch the real binlog, and cause the expire_logs_day to become invalid.

For the above detailed steps about the master-slave configuration of Mysql, do you find it very helpful? If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.