Master / Slave replication in MySQL is a great way to store an exact replica of your database on another machine in another location as part of a disaster recovery plan. Before setting up Master / Slave replication there are a few things to remember.
Writes – Writes to the master database should make it to the slave. But writes to the slave will not make it to the master. If you do write records to the slave database directly, be prepared to have to either recreate the records or back them up separately and recover them if the replication breaks. Many times the only way to get the databases to replicate again is to backup the master and recover it over the top of the slave deleting anything that was in the slave database before.
Broken Replication – Writes made directly to the slave can cause the replication to break due to duplicate key rows, etc.. Always write to the master.
Reads – Reads should be possible from either server. Many organizations will use replication so as to create another database to read from thereby taking the load of all of their select statements and reports off the master server.
Master Slave MySQL Replication
Steps to Setup MySQL Master / Slave Replication
Prerequisites
We will be assuming that the following prerequisites are done prior to beginning the steps listed below:
MySQL has been installed on both the master and the slave servers
The slave server is able to communicate directly to the mysqld port (typically 3306) on the master server, meaning that there is no firewall, routing, NAT or other problems preventing communication.
You have an administrator MySQL user that can create users on both the master and the slave machines.
You have permissions to edit the /etc/my.cnf files on both machines and enough privileges to restart mysql.
I‘m a new Ubuntu Linux user and my cloud hosting company installed MySQL server by default. I need to remove it and delete it from my server as I have no use of MySQL server. How can I uninstall MySQL on a Ubuntu based systems?
Typically following Mysql packages are installed on the Debian or Ubuntu Linux systems:
mysql-client – The latest version of MySQL database client.
mysql-server – The latest version of MySQL database server.
mysql-common – MySQL database common files.
How do I uninstall Mysql server?
Just use the apt-get command as follows remove both MySQL server and client in Ubuntu Linux:
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following packages were automatically installed and are no longer required:
linux-headers-3.2.0-31-virtual linux-headers-3.2.0-31
Use 'apt-get autoremove' to remove them.
The following packages will be REMOVED:
libdbd-mysql-perl* libmysqlclient18* mysql-client* mysql-client-5.5* mysql-common* mysql-server*
mysql-server-5.5*
0 upgraded, 0 newly installed, 7 to remove and 0 not upgraded.
After this operation, 67.5 MB disk space will be freed.
Do you want to continue [Y/n]? y
(Reading database ... 105097 files and directories currently installed.)
Removing mysql-server ...
Removing mysql-server-5.5 ...
mysql stop/waiting
Purging configuration files for mysql-server-5.5 ...
Removing mysql-client ...
Removing mysql-client-5.5 ...
Removing libdbd-mysql-perl ...
Removing libmysqlclient18 ...
Purging configuration files for libmysqlclient18 ...
Removing mysql-common ...
Purging configuration files for mysql-common ...
dpkg: warning: while removing mysql-common, directory '/etc/mysql' not empty so not removed.
Processing triggers for ureadahead ...
Processing triggers for man-db ...
Processing triggers for libc-bin ...
ldconfig deferred processing now taking place
Ce tutoriel a été intégralement repris du site http://www.starbridge.org et a été réalisé par tonio. Il est distribué sous licence creativecommons
Cet article, initié en 2007, est mis à jour régulièrement. Le système sur lequel est basé ce document est une DEBIAN stable (Wheezy). Le tuto est aussi entièrement compatible avec la version Testing (Jessie).
Ce tuto fonctionne également sous Ubuntu mais certains paquets présentent de légères différences. On essaiera de les indiquer si possible.
--master-data ajoute un CHANGE MASTER TO dans le dump contenant les informations nécessaires au slave sur les logs (nom de fichier et position). Cette option implique--lock-all-tables qui bloquera toutes les tables pendant le dump.
Autoriser les connections MySQL distantes
Activer les logs binaires sur chaque serveur : log_bin = /var/log/mysql/mysql-bin.log dans le format mixed : binlog_format = mixed
Positionner un server-id différent sur chaque serveur (a priori, ne pas utiliser 0…)
Créer un utilisateur dédié pour la réplication sur chaque serveur avec le droit REPLICATION SLAVE :grant replication slave on *.* to repl@'%' identified by 'XXX';
Master Slave MySQL Replication