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.

Making a MySQL table readonly to a user anywhere and for one tables only

mysql

There are some ways to ensure that MySQL tables are setup for readonly users with only certain access. This short snippet will explain the best way to do this.

mysql> CREATE USER ro_user;
Query OK, 0 rows affected (0.04 sec)

mysql> SET PASSWORD FOR ro_user = PASSWORD ('onionbag');
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT ON somedb.sometable TO ro_user;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE user SET Show_db_priv='Y' WHERE User='ro_user';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)

The above will create a default entry (‘%’) for all hosts, but we also want to ensure we add one for localhosts.


insert into user values ('localhost','ro_user','Y0UR_ENCRYTPED_PASSW0RD','N','N','N','N','N','N','N','N','N','N','N','N','N','N','Y','N','N','N','N','N','N','N','N','N','N','N','','','','',0,0,0,0);

MySQL dump without stopping transactions

mysql

When I needed to take a mysql dump from one database, I needed to make sure that it wasn’t going to bring down the database and thus damage the application.
With a few MySQL options, this can be avoided:
mysqldump -uroot –single-transaction –routines –triggers databasename | gzip -c | ssh root@some-server “cat > /folder/databasename.sql.gz”

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

MySQL insert into user database failure

mysql

I kept getting an error for “Column count doesn’t match value count at row 1″.

My MySQL [5.0.96] version probably had some extra columns. So…

INSERT INTO user VALUES (‘localhost’,’root’,password(‘newpassword’),’Y’,’Y ‘,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’, ‘Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,” ,”,”,”,0,0,0,0);

mysql remote connection kept failing

iptables, mysql

My connection to a remote server on a local network kept failing. Of course the problem was iptables. The error for MySQL was an error code 113.

iptables -I RH-Firewall-1-INPUT -p tcp –dport 3306 -j ACCEPT
iptables -I RH-Firewall-1-INPUT -p udp –dport 3306 -j ACCEPT

MySQL limited records in mysqldump

mysql

When I wanted to mysqldump a limited amount of records, I used the below command to limit my mysql dump to just 1 million records.

mysqldump -uroot -hsome-db02 -P3313 a_shard_07 table –where=”TRUE ORDER BY table_id LIMIT 1000000″ | gzip -c | cat > /db/disk/myDump.sql.gz