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

MySQL's built-in replication function is the basis for building large, high-performance applications. Distribute the data of MySQL to multiple systems. The mechanism of this distribution is to copy the data of one host of mysql to another host (slave) and execute it again.

During replication, one server acts as the master server, while one or more other servers act as the slave server. The master server writes updates to the binary log file and maintains an index of the file to track the bad logs, which can record updates sent to the slave server. When a slave server

When connecting to the master server, it informs the master server of the location of the last successful update read by the slave server in the log. Receive any updates that have occurred since then from the server, then block and wait for updates notified by the master server.

It should be noted that:

When mysql replication occurs, all updates to the tables in the replication must occur on the primary server. Otherwise, care must be taken to avoid conflicts between user updates to the tables on the master server and updates to the tables on the slave server.

Source code installation AliSQL-AliSQL-5.6.32-9

You can download the package on my network disk.

Link: https://pan.baidu.com/s/1FDmGV2zVLI9NxfdpjVqK4g

Extraction code: tpuy

After copying this content, open the Baidu network disk, the operation is more convenient:

The master-slave database is not synchronized in real time, and even if the network connection is normal, there is instant master-slave data inconsistency.

If you need multiple master libraries, you can use a ring configuration, so that the changes of any node can be synchronized to all nodes, so you also need ntp (time synchronization).

Source code installation AliSQL

Next, let's install AliSQL in source code.

Download the dependency package and extract it.

Wget http://repo.openfusion.net/centos7-x86_64//perl-Data-Dumper-2.154-1.of.el7.x86_64.rpmrpm-ivh perl-Data-Dumper-2.154-1.of.el7.x86_64.rpmyum-y install cmake bison git ncurses-devel gcc gcc-c++

Decompress AliSQL

Unzip AliSQL-AliSQL-5.6.32-9.zip

Create a group of mysql

Groupadd mysqluseradd-g mysql mysql

Create a mysql directory and a data directory

Mkdir-p / data/server/mysql/datacd / usr/local/src/AliSQL-AliSQL-5.6.32-9

Compile the specified path to the mysql under the data server directory

Cmake-DCMAKE_INSTALL_PREFIX=/data/server/mysql-DMYSQL_UNIX_ADDR=/data/server/mysql/mysql.sock-DDEFAULT_CHARSET=utf8-DDEFAULT_COLLATION=utf8_general_ci-DWITH_INNOBASE_STORAGE_ENGINE=1-DWITH_ARCHIVE_STORAGE_ENGINE=1-DWITH_BLACKHOLE_STORAGE_ENGINE=1-DMYSQL_DATADIR=/data/server/mysql/data-DMYSQL_TCP_PORT=3306-DMYSQL_USER=mysql-DENABLE_DOWNLOADS=1make & & make install

Pay attention to modifying the owner and group of the mysql directory

Chown-R mysql:mysql / data/server/mysql/data/chown-R mysql:mysql / data/server/mysql/cd / data/server/mysql

Initialize the database

Scripts/mysql_install_db-defaults-file=./my.cnf-user=mysql

Modify the owner and group of the generated file

Chown-R mysql:mysql my.cnf

Then open the mysql service

Cd / data/server/mysql/support-files./mysql.server start

Enter the interactive environment after opening it.

Cd / data/server/mysql/bin./mysql-u root-p

Here we are going to install both machines with AliSQL-AliSQL-5.6.32-9 under / data/server

Master library machine ip:192.168.124.107 slave library machine ip:192.168.124.108

Master main library configuration

Vim / data/server/

# modify content

# explanation: unique identity of server-id service (must be different between master and slave); log-bin startup binary log name is mysql-bin

[mysqld] server-id=1log-bin=mysql-bin

Save exit

Cd / data/server/mysql/support-files./mysql.server start

1. Add the slave account to the master master database

Go to the bin directory and start mysql to enter the interactive environment

Cd / data/server/mysql/bin./mysql-u root-p

Create a new user chaoge for master-slave synchronization, and the slave library allowed to log in is' 192.168.124.108'.

Create user 'chaoge'@'192.168.124.108' identified by' redhat'

Authorize the slave account, indicating the permission given to chaoge to copy from the library, on the 192.168.124.108 machine.

Grant replication slave on *. * to 'chaoge'@'192.168.124.108'

Check the replication account created by the main database

Select user,host from mysql.user

Check the permissions of the authorized account

Show grants for chaoge@'192.168.124.108'

Check the status of the main library

Show master status

2. Slave slave library configuration

Vim / data/server/mysql/my.cnf

Restart the mysql database

Cd / data/server/mysql/support-files./mysql.server start

Enter the mysql bin directory and enter the interactive environment

Cd / data/server/mysql/bin./mysql-u root-p

Check the parameters of the Slava slave database

Configure the copied parameters, and Slave connects to the configuration of the Master master library from the library.

Mysql > change master to master_host='192.168.124.108',master_user='chaoge',master_password='redhat',master_log_file='mysql-bin.000001',master_log_pos=120

Start the synchronization switch of the slave library to test the master-slave replication

View replication status and see

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Then it worked.

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.