Order:There are many similar articles on the Internet, but also a variety of Baidu came out, but for most of the first contact with MySQL master-slave small white, the online article code inside many technical points are not understood, there is a fall hit mistakenly hit, but most are this article card master, change article next card. - -。 Before I actually start to write a tutorial, I hope you can read it completely before you hit the code. The method applies to versions after MySQL 5.1. Before the version, self-Baidu.The master and slave of MySQL is a virtue I will not explain. Otherwise you will not be able to search this article.
Environment: W7 64 bits. MySQL 5.5.24 ... (which is the version of the Wamp package that most people pack) should actually do it in Linux, but just to understand, learn this master-slave, most people or Windows platform, so ... Not explained. First of all you have to install a MySQL instance under your windows (don't imagine a MySQL instance, get two libraries and then they configure Master-slave, this I have not played, interested students can try), meaning you want to assign a different port. The process of installing multiple MySQL under Windows is a good way to see this article. http://blog.csdn.net/zuxianghaung/article/details/7272557
This is the software package Http://yunpan.cn/cySt9WkiiTDPa extract code 42e8 (see how I conscience, even the software is ready for you, do not have to go to the big garbage download station to download.) Pay attention to your environment again, guarantee the same as mine, and database version)
OK. I'll just do it. You have configured the second MySQL instance.
The following two bat file codes are used to help you quickly start shutting down your new serviceStartmysql.bat
@ECHO OFF
net start mysql5.5
Pause
Stopmysql.bat
@ECHO OFF
net stop mysql5.5
Pause
This mysql5.5 is the service name of your second instance, stop stop service, start open service, do not explain.
Don't forget to go in your second MySQL instance.
Enter the dinner: Because we are configured under a Windows, so there are no online those master-slave IP. It's all localhost.Primary DatabaseMy.ini Add the following
Add the configuration data under [mysqld]:server-id=1 #配一个唯一的ID编号, 1 to 32. Manual SettingLog-bin=mysql-bin #二进制文件存放路径, don't care why you don't have a path name, you write like this.
#设置要进行或不要进行主从复制的数据库名 and set it up in slave (that is, your library). binlog-do-db= the master-slave database name 1, the master-slave database name 2 binlog-ignore-db= does not participate in the master-slave database name, does not participate in the master-slave database name 2 Save and restart the database service.
The meanings of these configurations above:
-Server-id As the name implies is the server identification ID number
-log-bin Specifies the log type
-Binlog-do-db is the name of the database you need to copy, if there are multiple just use commas "," separate
-Binlog-ignore-db is the name of the database that does not need to be copied, if there is more than one comma "," separate
Create a user in the main library (specifically for the connection from the library, note that this is built in the main library, do not be confused to the command interface from the library):
1.mysql>grant replication Slave,reload,super on *.Lisimin@LOcalhostIdentified by 'Root‘ ;
2.mysql>flush privileges;
3.mysql>show Master status; # Find the value of file and Position record down;
Explain the first sentence briefly. Created an account to copy from." @" in front of "lisimin" is the user name, followed by a valid domain, here because is local, so is the write localhost, if it is another address, corresponding to fill in the IP can, but should not consider the port problem, I created when there is no write port. The "root" behind by is the password. The account password is defined by itself and does not conflict with root and your current user name.
2.flush .... Refresh permissions. 3. This is your log value.The following article is about creating user-assigned permissions issues, just a quick look at the line.
Http://blog.chinaunix.net/uid-20639775-id-3249105.html
from the library configuration:from the database configuration My.ini:[Mysqld]server-id=2 #唯一#设置要进行或不要进行主从复制的数据库名, and also set on master. replicate-do-db= the master-slave database name 1, database name 2 replicate-ignore-db= does not perform database name 1, database name 2
multiple databases are used, split. You can actually write it like this . replicate-do-db= the master-slave database name 1
replicate-do-db= the master-slave database name 2
The one on top is also.
In fact, you only need to write the master-slave library name on it. That's right. If your main library is called A. You'd better call a from the library, too. It is possible to call something else, but it must be there.
Log in from your library below:Mysql>change Master to master_host= ' 127.0.0.1 ', master_user= ' slave ', master_password= ' slave ', master_log_file = ' mysql-bin.000001 ', master_log_pos=107;
master_host= here to fill in your main library's IP. master_user= ' lisimin ' The user we created just now. master_user= ' root '. Not explained. This is the show master status in the main vault, and the value we got. Fill in according to the actual situationmaster_log_file= 'mysql-bin.000015'master_log_pos=107
If there are other ports in your main library,master_port= Port numbermaster is commonly used in these parameters, the rest of their own Baidu. common to have, let you stop slave first. then you mysql>stop slave first. Execute the above code again. Other errors, easy to appear in grammar, punctuation,then Mysql>start slave;mysql>show slave status\g;if it appears:Slave_io_runing:yesSlave_sql_running:yesthat means success, and if something goes wrong, it's usually a connection problem. Re-check if you have entered the username and password you just created. Well, that's basically the problem.
respect for the original, some information, but also from the following two articles in the reference, http://blog.csdn.net/zhangking/article/details/5662545
http://blog.sina.com.cn/s/blog_6954c2c401017vvp.html
Finally say a little note, from the library to create a good, and inside the table structure, anyway, I was to create a good table structure, if you say later involved in adding, delete field problems, that is the future of the matter.
Also, if you really deploy to the server, it is generally Linux must write a time to delete the log file script file, this estimate is the future. Otherwise, the log file is very large. Make a regular backup of what.
OK, so you can add a record to your main database and try to see if you have a log of logs from the library. (Do not forget to open the service from the library,,) and, you can design the table inside the main library as InnoDB. From library design to MyISAM. To improve performance.
No more wordy.
Multiple MySQL instances under Windows configuration Master-Slave
