Build a MySQL master/slave database using the local environment _ MySQL

Build a MySQL master/slave database bitsCN.com using the local environment

Build a MySQL master/slave database using the local environment

First, let's introduce my configuration environment.

On the local machine, the IP address of MySQL5.5 is 192.168.1.101 (this address is self-applicable and will change as your work location changes, but no matter how it changes, you only need to make your virtual machine-ensure that you can PING it. OK)

Since I don't have any computers, I decided to create another MySQL instance in the virtual machine.

The OS of the VM is CentOS5.5. MySQL is 5.1.18.

Next, ensure that the host and virtual machine can communicate with each other. we need to know the IP address of the virtual machine. run the ifconfig command in the virtual machine linux to view the ip address of eth0. find the second line, inet addr: 192.168.1.115 Bcast: 255.255.255.255

Ping 192.168.1.115 to host XP and then ping 192.168.1.101 OK to VM.

Modify the configuration file of the master-slave MySQL instance.

I use host XP as the master database VM centos as the Slave Database

Master database configuration file

Run show variables like 'basedir' on the console if my. ini in the MySQL installation directory does not know the installation directory.

Locate # server section [mysqld]. The previous # server section indicates that the following configuration is for the MySQL SERVER.

Slave database configuration file

Yes. The/etc/my. cnf/etc folder stores various linux configuration files. The apache php configuration files are also stored here

Also find [mysqld]

Now we have opened the configuration file of the master-slave database and found a suitable location for writing configuration items.

We will write server-id = 1 in my. ini and server-id = 2 in my. cnf. you can refer to other materials if you do not understand the meaning of these configuration items. Here, server-id indicates that a unique id is assigned to the server. the master database is set to 1 and the slave database is set to 2.

Next, add the following options in my. ini:

Log-bin = filename. n // enable the binary log function. filename. n indicates that the log file name must be writable.

Binlog-do-db = dbname // write the changes of the given database into the log, that is, the database to be synchronized

Binlog-ignore-db = dbname // do not write the changes of the given database into the log, that is, the database that does not need to be synchronized

Before continuing to write configuration items to my. cnf, we need to create a synchronization user in the primary database. the command is as follows:

Grant replication slave, reload, super on *. * to 'yongbaolinux '@' % 'identified by '123 ';

This is a command for creating database users and corresponding permissions. For more information, see the manual and Baidu.

Next, add the following options in my. cnf:

Master_host = 192.168.1.101

Master_user = yongbaolinux

Master_password = 123456

Restart the master and slave databases separately.

Run mysql> start slave; mysql> show slave status/G

If you are lucky, you can see the following key information:

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.1.101

Master_User: yongbaolinux

Master_Port: 3306

.............

Slave_IO_Running: Yes

Slave_ SQL _Running: Yes

If you are not lucky, you can only see this.

Slave_IO_State:

Master_Host: 192.168.1.101

Master_User: yongbaolinux

Master_Port: 3306

..............

Slave_IO_Running: NO

Slave_ SQL _Running: NO

The first column is blank, that is, there is no connection to the master, and slave_io is not running. I thought it was a configuration file problem because the machine restarts the router and assigned a new ip address 192.168.1.102 to me. modify master_host to 102 in cnf

However, after the database is restarted, it is found that the output information has not changed. the first row is still empty. the second row of master_host is still 101.

Does my. cnf not work in this configuration file? Later, I deleted it and MySQL still started normally. I have to say-I am messy.

My world view, outlook on life, and love View collapsed completely at this moment. after verification by many parties, MySQL can indeed be separated from my. cnf's dependency, because MySQL can rely on default Startup parameters.

This is so swollen that you can only modify the master information in the console (later I learned that the flush command is actually used)

Mysql> stop slave;

Mysql> change master

> Master_host = '1970. 168.1.102 ',

> Master_user = 'yongbaolinux ',

> Master_password = '000000 ';

Mysql> start slave;

Mysql> show slave status/G

OK. Now everything is normal. the output information has been modified.

About Slave_IO_Running and Slave_ SQL _Running, there are a lot of nonsense to talk about.

Each master/slave system has three threads to interact to complete synchronization. one of the master and the other are slave_io and slave_ SQL. If a master node is connected to multiple slave instances, the master node must have the same number of master threads as the slave node, and each slave must have only one slave_io and one slave_ SQL. I already explained it. I don't know what else I can do.

The output information above shows that slave_io_running is NO, indicating that this thread is not started.

These three threads interact like this: first, after I/O is created, it will connect to the master and require the master to send the statements in the binary log. this binary log will be left to the end. in a long article, the master thread will handle this reasonable requirement, and then slave_io will read the statements passed by the master and copied them to the relay logs in the data directory) it can be seen that this slave_io has two things to do. one is to send a request (if this can be understood) and the other is to read and save the data. finally, slave_ SQL will read the relay log (delay logs) to update data.

For the binary log, mysql has multiple log formats, and binary is one of them. whether it is win or linux, the binary log is disabled by default. cnf or my. write log-bin = path in ini, and path is the log storage path. if you do not write the path and only write one file name, the log file will be saved to datadir, and win is C: /Documents and Settings/All Users/Application Data/MySQL Server 5.5/data, linux is/var/lib/mysql. After the configuration file is modified, restart mysql. you will find xxx in the preceding two folders. index and xxx.000001, xxx is your custom file name, xxx. index is the log index file, and xxx.000001 is the first log file, which will be incremented by the serial number in the future. (If you do not specify anything, the default log file name is mysql-bin)

The binary file cannot be viewed normally. you need the mysqlbinlog tool (a command in linux)

In Windows, open the DOS console and run mysqlbinlog xxx.000001 in the C:/Program Files/MySQL Server 5.5/bin directory. (in linux, run the mysqlbinlog command directly, [root @ localhost xxxx] # mysqlbinlog xxxxx.000001 ;)

There are no SQL statements before the synchronization operation. if you have performed operations on the primary database, you will find the corresponding SQL statements in it.

There are a few nonsense words before closing the job

If the data table structure of the master-slave database is different, for example, if one table is missing from the slave database or a field is missing, the data operations on the master database are not responded to by the DML statement operation on the Slave Database. Obviously, they do not exist. how to add data to the table? however, because the relay log contains the DML statements, if you make the database structure of the slave machine the same as that of the host, the data will be automatically synchronized-restart required. slave Database

The definition of data objects for DDL operations on hosts, such as adding a table to delete a table, is automatically performed on the slave machine, or is it obvious that DDL can be executed on the slave machine? of

Now, you can add, delete, modify, and query databases on the host. the database automatically changes to master-slave replication.

PS: on the Internet, some gods say that the one-master-Multi-slave architecture is not the best architecture, but I do not know what the best architecture is. I hope you will not be able to advise me that I will only use mysql after the master-slave synchronization- proxy for read/write splitting

BitsCN.com