20150410--lnmp+nginx-01

Catalogue

first, what is master-slave replication 1

ii. Principle of realization 1

Third, user authorization, 2

1 , add a user, grant the user 2

2 , if you delete a user 3

Four, Log-bin Log 3

1 , how to open Log-bin Log 3

2 , how to View The contents of the Log-bin log. 4

3 , Log-bin Some command operations related to logs 5

4 , using Bin-log Log Complete one case, recover data for a case 6

v. Master/slave configuration 8

1 , configuring the primary server: 8

2 , configuring Slave server 9

3 , how to revoke from server 10

six, read and write separation to achieve the first, what is the master-slave replication

At least two database servers, you can set the master server and the slave server separately, and any operations on the master server will be synchronized to the slave server

Second, the realization principle

There is a log in MySQL called the bin log (binary log), which records all SQL statements that have modified the database. The principle of master-slave replication is actually multiple servers open the bin log, and then the primary server will be executed SQL statements recorded in the Bin log, and then from the server to read the bin log, the contents of the log to save to their own relay log, The SQL statements recorded in the relay log are executed again from the server. This way the data from the server is the same as the primary server.

The implementation of the technology is: the user's authorization, and Log-bin log open. third, the user authorization, 1, add a user, to the user authorization

View the user situation below the current database.

Grant permission name (select Update Delete) on the database name. Table name to ' user name ' @ ' host ' identified by password

For example, the authorized user Xiaolong can only log on to the 192.168.10.120 host and perform a select update delete operation on the User Library user table only.

Grant Select Update Delete on User.user to ' Xiallong ' @ ' 192.168.10.120 ' identified by ' 12345 ' 2 If a user is deleted

Drop user ' username ' @ ' host '

iv. log-bin Log 1. How to open Log-bin log

Open the Mysql.ini configuration file

The Log-bin log file that was created.

2, how to view the contents of the Log-bin log.

Using a command,

Mysqlbinlog--no-defaults log file name and path

Log-bin record the format location of the SQL statement

3. Some command actions related to Log-bin log

Flush logs generates a new Log-bin log

Reset Master: Empties the previous Log-bin log and generates a new Log-bin log

Show master status; View the latest Log-bin logs, including the latest POS locations.

4, use Bin-log log to complete a case, a case of recovering data

Steps:

(1) Create a new table

CREATE TABLE Nihao (id int);

(2) Flush logs generates a new Log-bin log file

INSERT into Nihao values (1000)

INSERT into Nihao values (2000)

INSERT into Nihao values (3000)

(3) Flush logs produces a new Log-bin log file.

(4) Delete from Nihao

(5) Request for data recovery

A company, for example, backed up data once at 9:00. By 9:30, all the data was deleted due to the operator's misoperation. Required to restore data to 9:30.

such as the following: request recovery data

Idea: Open the Log-bin log file for analysis. Finds the POS location of the INSERT statement. Recovering data based on POS location

Use the following parameters

--start-pos= "Starting point of POS"

--stop-pos= ' End of Pos point '

v. Master and slave configuration 1. Configure the primary server:

(1) The primary and the server are to open the Log-bin log, each server set a unique value of Server-id. Restart the server.

(2) To authorize a user from the server, the user is granted replication permissions.

Grant Replication Slave on * * to ' xiaoming ' @ '% ' identified by ' 12345 '

(3) to see the name and POS location of the Log-bin log inside the primary server. (Stop operation on the primary server)

Show master status;

2. Configure the slave server

(1) Perform a stop slave stop from the server:

(2) The specific configuration:

Change Master to master_host= ' home server IP ', master_user= ' authorized user name ', master_password= ' authorized password ', master_log_file= ' Log-bin the name of the log file ', Master_log_pos=pos location;

(3) Turn on the slave server

Start slave

(4) View the status from the server:

show slave status;

Slave_io_running:yes

This process is responsible for reading the Binlog log from the server from the primary server and writing the relay log from the server.

Slave_sql_running:yes

This process is responsible for reading and executing the Binlog log in the trunk log,

Note: The above two is yes to indicate success, as long as the status of one of the processes is no, the replication process is stopped, and the cause of the error can be seen from the value of the "Last_error" field. 3. How to revoke the slave server

(1) Stop slave

(2) Reset Slave all

Vi. Read and write separation implementation

Design at the program level

Class mysql{

$DBM = Primary server

$dbs 1= from the server

$dbs 2= from the server

Public Function query () {

In the query inside the sentence to judge, analyze the connection of different MySQL server.

If the query is a random connection two from the server, if the modification operation, the connection to the master server

}

}

TP Framework supports read and write separation;

' Db_deploy_type ' =>1,// Distributed database Support

' Db_type ' = ' mysql ',//database type

' Db_host ' = ' localhost,192.168.3.250 ',//server address

' Db_name ' = ' php,php ',//database name

' Db_user ' = ' root,xiaogang ',//user name

' Db_pwd ' = ' root,1234 ',//password

' Db_port ' = ' 3306 ',//Port

' Db_prefix ' = ',

' db_rw_separate ' =>true,// supports read and write separations

Verify:

The SELECT statement is an operation from the server

Insert statement is the primary server of the operation

20150410--lnmp+nginx-01