Build a MySQL Master/Slave database using the local environment

Use the local environment to build a MySQL master-slave database. First of all, let's introduce my configuration environment. The local machine is equipped with the MySQL5.5 IP address 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 -- make sure it can be pinged) www.2cto.com because I don't have any computers, I decided to create another MySQL virtual machine in the virtual machine. The OS 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 that the IP address of the virtual machine is run the ifconfig command in linux of the virtual machine to view the ip address of eth0 and find the second line of inet addr: 192.168.1.115 Bcast: 255.255.255.255.255 Mask: 255.255.255.0 to ping 192.168.1.115 in host XP and then ping 192.168.1.101 OK in the VM. Now, modify the master and slave databases. In the SQL configuration file, I use the host XP as the master database Virtual Machine centos as the master database configuration file from the database www.2cto.com under the MySQL installation directory my. if ini does not know the installation directory, run show variables like 'basedir' on the console '; you can find # server section [mysqld]. The previous # server section indicates that the following configuration is for the MySQL SERVER. The slave database configuration file is/etc/my. the cnf/etc folder stores various linux configuration files. The apache php configuration files are also stored here. [mysqld]. Now we have opened the configuration file of the master-slave database and found it. we are in my. in ini, enter server-id = 1 in my. in cnf, if server-id = 2 does not understand the meaning of these configuration items, you can refer to other documents. Here, server-id indicates that a unique id is assigned to the server. The master database is set to 1, the slave database is set to 2, and then continues in my. add the following option in ini: log-bin = filename. n // enable the binary log function filename. n is the log file name. Ensure that you can write binlog-do-db = dbname // only write the changes of the given database into the log, that is, binlog-ignore-db = dbname // No write the changes of the given database into the log, that is, the database that does not need to be synchronized continues to my. before writing configuration items in cnf, we need to create a synchronization USER command in the master database: grant replication slave, reload, super on *. * to 'yongbaoslinux '@' % 'identified by '000000'; this is a command for creating database users and corresponding permissions. For more information, see the manual and Baidu. add in cnf The following options are master_host = 192.168.1.101master _ user = yongbaolinuxmaster_password = 123456. Then, restart the master-slave database and run mysql> start slave as root to enter the slave database of the VM; mysql> show slave status \ G if you are lucky, you can see the following key information: Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.1.101Master _ User: yongbaolinuxMaster_Port: 3306 ............. slave_IO_Running: YesSlave_ SQL _Running: if you are not lucky, you can only see Slave_IO_State: Master_Host: 192.168.1.101Mast Er_User: yongbaolinuxMaster_Port: 3306 .............. slave_IO_Running: NOSlave_ SQL _Running: the first column of "NO" is empty, that is, it is not connected to the master, slave_io didn't run either. I thought it was a configuration file problem because the machine restarted the router and assigned another ip address 192.168.1.102 to me. in cnf, The master_host value is changed to 102, but after the database is restarted, the output information is not changed. The first row is still empty, and the second row is still 101. Is it my. does the cnf configuration file work? Later I deleted it and MySQL still started normally. I have to say that I am messy. my world outlook, outlook on life, and my love view collapsed completely at this moment. After verification by multiple parties, MySQL can indeed be detached from my. the dependency of cnf is because MySQL can rely on the default startup parameters, but this is so swollen. 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 to> master_host = '2017. 168.1.102 ',> master_user = 'yongbaolinux',> master_password = '000000'; mysql> start slave; mysql> show slave status \ Gok 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 every pair of mas. There are three related threads in the ter/slave system to interact and complete synchronization. One of the main threads is the slave_io and the 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 explained it to the white, but I don't understand how to do it. The output information above shows that slave_io_running is NO, which indicates that the three threads not started in this thread 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) and execute them to update the data. This binary log, mysql has a variety of log formats, binary is one of them, whether it is win or linux, binary logs are disabled by default, it is very easy to enable as long as in my. 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) to open the DOS console in win, go to the C: \ Program Files \ MySQL Server 5.5 \ bin directory and run mysqlbinlog xxx.000001 (run the mysqlbinlog command directly in linux, [root @ localhost xxxx] # mysqlbinlog xxxxx.000001 ;) there are no SQL statements before the synchronization operation. If you have performed operations on the master database, you will find that the corresponding SQL statements are still a few nonsense words before the completion of the master database data table structure. for example, if there is one less table on the slave or a field on the slave, the data operation on the master is not responded to by the DML statement operation on the slave. Obviously, there is no way to add data to the table. but because the relay logs contain these DML statements, if you make the slave database structure the same as that of the host, the data will be automatically synchronized. You need to restart the slave database. when the host performs DDL, that is, data object definition operations, such as adding a table to delete a table, the slave machine will automatically perform the operations, or is it obvious that the DDL can be executed successfully on the slave machine? you 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