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

Edit the master configuration first:

Vim / etc/my.cnf

(add the red box. # server id section: each is distinguished by a unique id, which can be distinguished by the last three digits of the server ip. # binary log: log. # statement row mixed: three monitoring methods)

Three of these situations: listening for statement changes, disk changes, or mixed mode?

Then edit the / etc/my.cnf of slave:

(set up id, establish relay log)

Then start the master and slave: service mysqld start, respectively

Next, the master and slave will establish a connection:

Log in to mysql:

Mysql-uroot-p

Master sets up an authorized account (this account allows you to copy the binary log of master. One replication client account, one replication slave account):

Authorize a client account and a slave account. *. *: works on all libraries and tables, unlike mongodb, which can select a certain database and table. The to account repl, which specifies that the ip:@192.168.%.%, in which it works, that is, the prefix 192.168 can be used. Identitied by 'repl': specifies the password as repl)

So far, the log of the master server, the account number and the relaylog of the slave server are ready. You only need to notify the slave server to connect:

(master_log_file is the binary file currently used by master. Master_log_pos is the position of the current master. There are ways to view it below)

Take a look at the binary log:

The latest binary logs currently in use are:

(mater machine)

Look at the status of the current master machine:

(it is true that mysql-bin.000003,position 278 is currently used, which means: my main server has been written to 278, please pay attention to it from the server.)

Action slave:

(reset slave: resets slave. Then set the relevant information of the corresponding master)

Check out slave: show slave status\ G

Start slave:

Just check the status again (the first line becomes waiting for master to send event):

Next, no matter whether it is a new library or table in master, and all kinds of additions, deletions, modifications and queries, you will see the corresponding changes in slave.

Typical questions:

There is a business scenario, after testing, the read-write ratio is 1:20, please set the optimization scheme reasonably according to the read-write ratio.

Reading-writing ratio:

Ratio of write data to read data

Insert/update/delelte / select

Regardless of the specific technology, deduce the basic elements from the concept of separation of reading and writing.

In popular terms, the read server refers to the data mirror of the write server.

From the server side: there should be N slave servers and master servers to keep the data consistent.

From the client point of view: for example, there is an insert statement and a select statement

Then distinguish between read / write statements and request slave / master servers respectively.

The specific technology of server-side read-write separation

1: database cluster technology

Cluster consists of three concepts

1:sql node sql node

2: data node data node

3: manage node ndb managerment

Sql statement sends "1sql node", "1sql" node sends to "2 data nodes", and 3 management nodes complete the synchronization between data nodes.

Cluster technology is relatively complex, at least 3 nodes, 4 servers can be completed.

2: database replication replication

The implementation principle of Database replication replication

1: whenever the main server runs a statement, it generates a binary log binlog

2: continuously read the binlog of the master server from the server

3: the binlog read from the main service is converted to its own executable relaylog

4: execute relaylog

Implementation steps:

1: first make sure that the primary server turns on the binary logging function.

In this way, as soon as there is a data change on the main server, a binary log is generated immediately.

2: the slave server also needs to turn on binary log and relay log function.

This allows you to read binlog from the master server and generate relaylog

3: establish a slave account on the master server and grant a number of permissions.

4: specify the master server corresponding to the slave service and turn on the slave server.

Concrete implementation

I have two linux,IP of 199200 under my virtual machine.

1: 200 do slave server

2: 199mysql, as the main server.

3: ensure the interworking of master-slave port 3306.

4: configure the master server and open binlog

# create a unique id for the server

Server-id=1

# enable binary log

Log-bin=mysql-bin

# specify log format

Binlog-format=mixd/row/statement

Restart mysql

Has been able to act as a master server

5: configure to open binlog and relaylog from the server

Restart the slave server

6: create a corresponding replication account on the primary server

7: specify the master server to be replicated through the statement in the slave server (note that you can have one master and multiple slaves, not one slave and multiple masters).

8: start the slave server function

-> start slave

8: test.

Common sentences:

Show master status; check the status of master, especially the current log and location

Show slave stattus; checks the status of slave.

Reset slave; resets the slave state.

Start slave; start slave status (start listening for msater changes)

Stop slave; pauses slave statu

View the mysql account:

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.