Ubuntu MySQL live from the library building

1, first of all we want to determine a library from the repository, remember that only read from the library can not have other operations, if the operation is written by the master and slave failure, then see how we build the master-slave bar!

2, Environment: Ubuntu,mysql ( master-Slave database version must be consistent )

Primary server address:192.168.11.186 mysql root password:1111 database: Test

From server address:192.168.11.108 mysql root password:123

The database data must be consistent between the primary server and the server to be synchronized.

One, configure the main library

1.1 Change profile sudo vim/etc/mysql/my.cnf add a primary server-side configuration to its configuration file

Server-id=1 # server ID

Log-bin=/var/log/mysql-bin # binary file storage path

Binlog-do-db=erp_database # The database to be synchronized (erp_database is a database that needs to be synchronized, can be one or more databases )

Binlog-ignore-db=information_schema # ignore the unsynchronized database, this can not be written

Binlog-ignore-db=mysql # Ignore the unsynchronized database, this can not be written

1.2 Save my.cnf file, restart the database (restart command : sudo service mysql restart),

1.3 Enter the terminal:

Enter user name and password: mysql-u user name - p password (mysql-uroot–p1111)

1.4 to set permissions from the server to which you want to connect:

grant replication Slave on * * to [e-mail protected] identified by ' 123456 '; ( Here's a question for you to copy, to modify this single quote )

After giving a user, go to the main database and add any permissions to the user.

1.5 input Command show master status; # Find The value of File and Position record down;

1.6 Enter the flush logs update log file. ( no person is recommended unless you are connected to sync )

In fact, the primary server does not need to set too much information, only the first three lines in the first 1 is sufficient.

Two, configure from the library:

1. Change the profile sudo vim/etc/mysql/my.cnf to add the configuration file from the server side:

server-id=2 # server ID , cannot be consistent with primary server

Replicate-do-db=erp_database # databases to be synchronized

Binlog-ignore-db=information_schema # Ignore the unsynchronized database, this can not be written

Binlog-ignore-db=mysql # Ignore the unsynchronized database, this can not be written

Binlog-ignore-db=mysql # Ignore the unsynchronized database, this can not be written

Binlog-ignore-db=test # Ignore the unsynchronized database, this can not be written

2.2 Save my.cnf file, restart from database. Restart command : sudo service mysql restart

Third, export the database from the main library, and then import the data into the from database.

Four, binary files:

1. Package the conversion to TXT file in the main library:
Mysqlbinlog mysql-bin.000003 >/home/yxp/111.txt

2, then remotely send to the slave server , execute 111.txt file, log in MySQL, input:source 111.txt fill the binary file from the library data.

Five, in the brush binary files, do not quit MySQL, enter the stop slave;

Then enter:

Change Master to
Master_host= "192.168.11.18",
Master_user= "This is the username given above",
Master_password= "123456",
Master_log_file= "mysql-bin.000008",//This is where we brush the last binary file
master_log_pos=11034; Last value of the at of the last binary file

Six, after Setup, execute mysql> start slave; open slave thread; execute mysql> show Slave status\g ( no semicolon ), view

Slave_io_running:yes

Slave_sql_running:yes

This means that MySQL master and slave configuration is successful!!!

Ubuntu MySQL live from the library building