HomeХобби и стильRelated VideosMore From: Internet Services and Social Networks Tutorials from HowTech

How to Set Up MySQL Replication

135 ratings | 61452 views
In this easy step by step tutorial, you will learn how to replicate the contents of MySQL database. Don't forget to check out our site http://howtech.tv/ for more free how-to videos! http://youtube.com/ithowtovids - our feed http://www.Facebook.com/howtechtv - join us on Facebook https://plus.google.com/103440382717658277879 - our group in Google+ Introduction MySQL Replication basically allows the contents of the master database server in MySQL to be replicated to other database servers. In this tutorial we will show you how to set up MySQL replication through phpMyAdmin. Step#1: Getting Started with Replication To get started, first of all, log in to your php my admin panel, and then click on the Replication tab on the top. Step#2: Replication Plan From there, click on the Configure option, under the Master Replication section. With that done, the master configuration section will expand, having a list of databases and a replication plan as well. Step#3: Create new Database Here, you can either Replicate All databases while ignoring the ones selected, or the other way round, that is, ignore all the databases and replicate the ones which would be selected. Let's choose the second plan and then select the database named "test" over here. With that done, copy the lines shown below the list. These have to be added in the MySQL "my.ini" file. Step#4: MY.INI File Since we are using the XAMPP server, the "my.ini" file can be fetched by clicking on the Config button and choosing the "my.ini" option against the MySQL Module. Step#5: Setting up MY.INI File Once the file opens up, navigate to the "log_error" parameter, and just paste the copied lines beneath that. With that done, change the "max allowed packet" size to 16 MB. Once you are done with that, scroll down the file and comment out the "server-id" parameter with a hash sign. When you are done with that, save the file and then restart the services of both Mysql and Apache. Step#6: Master Replication Setup After that, switch back to your phpMyAdmin page, and hit the F5 key to refresh the panel. With that done, move over to the Replication tab again. There you will find that the Master Replication has been set up successfully. You can click on the Show Master Status option to see its status. Now, click on the Add Slave Replication user option to add a Replication user Step#7: Creating Replication User Specify the username and the host, and then click on the Go button. A replication user would be added as a result. Step#8: Slave Replication Setup Now open up the Replication tab again, and click on the Configure option under the Slave Replication section. Another page will open up, and from here, copy the Server id which has to be pasted in the "my.ini" file. So let's open up the "my.ini" file again and locate the "server-id " parameter which we had commented earlier. Over here, simply paste the Server id copied from the Slave configuration, save the file and restart the MySQL services. Step#9: Updating Slave Configuration Now switch back to the Slave Replication page again, and specify the user details which were created before. With that done, click on the Go button. A message will appear that the Master server had changed successfully. Step#10: Warning Messages Now, if you open the Replication tab once more, you will notice two warning messages appearing in the Slave Replication portion. One would be regarding the Slave SQL Thread and the other would be regarding the Slave IO Thread, both of which won't be running. To resolve these errors, simply click on the Control Slave option, and Start these threads one after another. You will notice that the warning messages would disappear. Step#11: Verifying Replicated database Now click on the Databases tab and here you can see that the test database has been replicated successfully. This means that we have setup and configured MySQL replication. And this is how to set up MySQL replication using phpMyAdmin.
Html code for embedding videos on your blog
Text Comments (16)
Mirza Adil Ali Baig (11 months ago)
You are doing replication on the same server. Where is the other server? You are editing same my.ini file. Is this master-slave replication or Master-Master replication? Video quality is very good but not helped me out.
sly fox (1 year ago)
how do you fix Last_Error: Could not execute Delete_rows event on table Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND;
Jay White (2 years ago)
Replication, along with many others, seems to be missing from PhpMyAdmin now. It is not visible to me.
neuroloops (2 years ago)
thank, i was stuck on this, I missed the server id on my slave :)
Roshad Smith (3 years ago)
Where is the database being replicated? i.e. we are replicating from the Master to a Slave but where is the location of the Slave database?
adrianTNT (3 years ago)
2:48 I click "Start SQL Thread only" and it says "Loading" forever :/
chris taylor (3 years ago)
Thank you thank you thank you thank you thank you!!! :)
She Fu (3 years ago)
i start the i/o thread and after i refresh it still shows Slave IO Thread not running! please help !
fausto andrea giannone (1 month ago)
same here
Majd Azzam (3 years ago)
+Kawtar Bennani bad news :\ .. Unfortunately I dont find the solution who can help us :\ :\
Kawtar Bennani (3 years ago)
+Majd Azzam same here, heelp heelp please :/
Majd Azzam (3 years ago)
+She Fu today I have the same problem.. could you please help me if you found the answer for this problem
Thinker Hardworker (4 years ago)
A nice video.   But not complete.   ( Only windows ) Comment from Programster are  right.  Any new person will get confused.  Following precautions are necessary. 1.  If you are running on Linux,  edit my.cnf  instead of the my.ini   ( location  -   /etc/mysql/my.cnf ) 2.  In the second instance,  you need to insert the server-id on a slave system  by editing /etc/mysql/my.cnf 3.  Replace ip number instead of localhost. 4.  On slave system  add following parameter replicate-wild-do-table=your-db.%   (replace your-table with actual database name followed with a period and % sign.  5.  Incase you counter any errors like duplicate key etc,  on slave system,  loging to mysql and give following command. set global sql_slave_skip_counter = 1;
haha666413 (4 years ago)
nice and everything but this is not the way for two servers with apache and mysql installed instead of xamp
Bille747 (4 years ago)
Wait what? Did you set the master server as a slave as well??
Programster (5 years ago)
1:15 For those thinking "WTF why are you commenting out the server id parameter?" Thats because it also gets set in the lines you copy/pasted from phpmyadmin a few seconds ago. Alternatively (and probably better), is you could just update the server-id from the one shown in phpmyadmin.

Would you like to comment?

Join YouTube for a free account, or sign in if you are already a member.