mysql

Easily fix MySQL replication

mysql

I found that when replication breaks and the slave has corrupted, it’s best to start from scratch. I use just two commands to be able to do this properly.

The first is performed on the master and copies over the specified database ‘dbname’ below to the slave machine via SSH.


mysqldump -uroot --single-transaction --routines --triggers dbname | gzip -c | ssh user@slave_machine "cat > /directory/dbname.sql.gz"

Simultaneously, make sure you run ‘show master status’ on the master and take a note of the position of the binlog prior to starting the dump.

Then, when you’re on the slave, make sure the replication has stopped by running ‘stop slave’. Drop any existing data that you have on the db, then import the new dump into MySQL.

On the master, using the same position and information you grabbed from running ‘SHOW MASTER STATUS’ on the master, update the code block below accordingly.


RESET SLAVE;
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=12345678;

You should then run ‘SHOW MASTER STATUS’ and watch the position catch up with the master and depending how far ‘behind’ replication is, you’ll need to wait for some time for replication to fix.

Setup LAMP and phpMyAdmin from scratch on Ubuntu 10+

apache, lamp, linux, mysql, php, phpmyadmin

It’s really easy to get a LAMP webserver up and running, so I thought I’d post some nice easy instructions for installing on a Ubuntu box.

1) sudo apt-get install mysql-server mysql-client
-> you will need to choose a MySQL Root user password

2) sudo apt-get install apache2
-> confirm in your browser that you are able to view a page for your IP address. This could be localhost, or your local IP address.

3) sudo apt-get install php5 libapache2-mod-php5
-> you’re going to need to install PHP next as above.

4) /etc/init.d/apache2 restart
-> …and of course an Apache restart so that PHP is activated

5) sudo apt-get install phpmyadmin
-> you’re going to be asked which webserver to configure. go for apache2. it’ll then probably request your password you selected for MySQL.

6) vim /etc/apache2/apache2.conf
-> use your favorite editor to open up the apache config file

7) Include /etc/phpmyadmin/apache.conf
-> add the following somewhere in the file.

8) /etc/init.d/apache2 restart

9) http://yourhomepage.com/phpmyadmin
-> test 🙂

Creating a second instance of MySQL on the same machine

mysql

It’s possible to run multiple instances of MySQL on the same machine, albeit running on a separate port. This guide attempts to explain quickly and easily how it’s done.

So what do we do? First things first, we need to make the data directory for the new mysql2 instance. That can be easily found from running ‘ps’ and identifying your current instance of mysql, let’s call it mysql1 in this case.

In my case, we had /var/lib/mysql, so I created mysql2:
mkdir /var/lib/mysql2
chown -R mysql.mysql /var/lib/mysql2

I also want some separate log file destinations:
mkdir /var/log/mysql2
chown -R mysql.mysql /var/log/mysql2

Then we get into the nitty gritty! Changing the my configuration file. That’s located on Debian systems in /etc/my.cnf and then I copied it to /etc/my2.cnf

In the new second config file, you need to specify a new port. Let’s say the default port for the first instance is 3306, I now should add “port=3307” into my2.cnf

The same rule will apply for changing the ‘datadir’ to the new directory in the config file, as well as the socket, you need to create a new connection socket for the secondary instance.

Also inside of the config file, you should specify where your new log-error file is as well as a new pid-file (if these are already in my.cnf, that is).

When you’re happy, just flick through the my2.cnf and double check that you’ve configured everything properly. Mine looked fine, so onto the next step.

run:
mysql_install_db –user=mysql –datadir=/var/lib/mysql2/

You’ll see some output on the command line, it should hopefully have no ERRORs in there. I also like to just double check my other instance of MySQL is still up and working OK using ps.

Now, let’s start our instance, shall we?
mysqld_safe –defaults-file=/etc/my2.cnf &

Verify it works:
mysql -S /var/lib/mysql2/mysql2.sock