MySQL

I've had to do mysql database restores based on lvm snapshot backups, one too many times... from damaging sql statements. So here is some notes from the last one for future reference:

* Prior to restore, make sure no writes are coming to the corresponding db schema.

* Restore the database schema from the daily archived snapshot:

Note: The above process is for database schema with MyISAM tables only, so just restoring the corresponding db folder was sufficient.

The snapshot.log file has the status of the master/slave replicated positions shown via "show master status\G" and "show slave status\G". Additionally, I also pipe in the file sizes of the mysql bin logs at the time the lvm snapshot was taken.

Once the database has been restored from the daily snapshot archive, begin restoring from the binary logs to bring the data up to date.

* Extract from bin log using position recorded in the snapshot.log file:

* Edit the db1.sql file and remove the unwanted transaction.

Note: If you do not have exact positions, you could playback the logs with an approximate start and end date in the below format:

* However, If duplicate error occurs, change "INSERT" to "INSERT IGNORE" or "REPLACE" to overwrite:

* Check to make sure that no other database schema is being used, if found you will have to remove those lines as well if playing back just a single schema:

* Run import:

With that import, the datbase schema should be caught up and restored back to it's current state with the unwanted transaction/s removed.

Below script checks and kills any sql SELECT statements that has been running for more than 60 seconds. The script can be added to a cron task to be run periodically. Make sure to check the log file it creates to keep an eye on the long running sql statements and tune it.

Prior to running it, setup the root login in "~/.my.cnf" file with the login credentials:

• munin-node installed via epel repository.
  
• Install perl-Cache-Cache:
  
  # yum install munin-node perl-Cache-Cache
• Create file "/etc/munin/plugin-conf.d/mysql" with below contents:
  
  [mysql*]
env.mysqluser munin
env.mysqlpassword {PASS}
• Create mysql user and assign corresponding privileges:
  
  mysql> create user munin@localhost identified by '{PASS}';
mysql> GRANT PROCESS, SUPER ON *.* TO 'munin'@'localhost';
mysql> GRANT SELECT ON `mysql`.* TO 'munin'@'localhost';
mysql> flush privileges
• Check the suggestions and install the plugins:
  
  munin-node-configure  --suggest 2>/dev/null |grep mysql
munin-node-configure  --shell | grep mysql | sh
• Test via:
  
  # cd /etc/munin/plugins
# munin-run mysql_connections

With a recent OS upgrade, some of the mysql database tables got corrupted. Below is how I was able to get it repaired.

1. Stop mysql server.
   
2. Once mysql server is stopped, run a repair on all of *.MYI files via myisamchk:
   
   # myisamchk -r /var/lib/mysql/*/*.MYI
3. Bring up the mysql server.
   
4. Run a mysqlcheck of all databases via:
   
   # mysqlcheck -c --all-databases | tee /tmp/dbcheck.log
5. Grep for "error" on the log and proceed to create a sql file to be run to repair the tables.
   
   # grep error -B1 /tmp/dbcheck.log | grep -v "error\|--" | sed 's/\(.*\)/REPAIR TABLE \1;/' >/tmp/dbrepair.sql
6. The file output should be something like:
   
   REPAIR TABLE database1.table1;
REPAIR TABLE database1.table2;
REPAIR TABLE database2.table1;
7. Log into mysql and source the repair script:
   
   # mysql> source /tmp/dbrepair.sql
8. That should run and repair all of the corrupted tables. Verify by running another check and maybe an extended one.
   
   # mysqlcheck -c -e --all-databases

First enable logging of slow running queries in "mysqld" section of "my.cnf".

Once queries get logged, you can get the top 10 queries sorted by number of occurrences in the log via:

Here is how to give dba user sudo access as mysql user.

visudo and add:

Basically, it defines that user "dbauser" can run all commands as "mysql" user.

For the dbauser to get mysql shell, run:

MySQL replication stops whenever there is an error running a query on the slave. This happens so the problem query can be identified and resolved.

Such errors can be skipped as long as you know why the query failed.

For example, when you run a query in the slave accidentally instead of running it on the master. You can skip just that one query that is hanging the slave using:

"wait_timeout" is the amount of seconds during inactivity that MySQL will wait before it will close a connection on a non-interactive connection.

"interactive_timeout" is the same, but for interactive mysql shell sessions.

Setting a value too low may cause connections to drop unexpectedly, specifically if you are using persistent connections. Setting a value too high may cause stale connections to remain open, preventing new access to the database.

For wait_timeout, this value should be set as low as possible without affecting availability and performance.

For interactive_timeout, changing this value won't really increase or decrease performance of your application.

You can now get the latest PHP with mysqlnd (MySQL Native Driver) support via remis' yum repository as mentioned in PHP-5.3,-zts-and-mysqlnd. The blog mentions that this is enabld in php-zts. However, no php-pecl extension are available and neither are some of the extensions thread safe. So I went about rebuilding php package from source for mysqlnd support.

1. Download the source php rpm from http://rpms.famillecollet.com/SRPMS/ . Note, I have used php-5.3.2 which is the latest as of this writing.
   
2. Install and apply the below diff patch to the spec file via `patch -p0 < {new_patch_file}` where "new_patch_file" has the below contents:
   --- php-5.3.2-remi.spec.orig 2010-03-11 23:07:04.000000000 -0600
+++ php-5.3.2-remi.spec 2010-03-11 23:36:03.000000000 -0600
@@ -24,6 +24,7 @@
%global phpversion 5.3.2

# Optional components; pass "--with mssql" etc to rpmbuild.
+%define with_mysqlnd %{?_with_mysqlnd:1}%{!?_with_mysqlnd:0}
%define with_oci8 %{?_with_oci8:1}%{!?_with_oci8:0}
%define with_ibase %{?_with_ibase:1}%{!?_with_ibase:0}
%if %{?rhel}%{?fedora} > 4
@@ -677,6 +678,11 @@
%if %{?fedora}%{?rhel:99} >= 10
         --with-system-tzdata \
%endif
+%if %{with_mysqlnd}
+        --with-mysql=shared,mysqlnd \
+        --with-mysqli=shared,mysqlnd \
+        --with-pdo-mysql=shared,mysqlnd \
+%endif
$*
if test $? != 0; then
   tail -500 config.log
@@ -704,8 +710,13 @@
       --enable-dba=shared --with-db4=%{_prefix} \
       --with-xmlrpc=shared \
       --with-ldap=shared --with-ldap-sasl \
+%if %{with_mysqlnd}
+      --with-mysql=shared,mysqlnd \
+      --with-mysqli=shared,mysqlnd \
+%else
       --with-mysql=shared,%{_prefix} \
       --with-mysqli=shared,%{_bindir}/mysql_config \
+%endif
%ifarch x86_64
       %{?_with_oci8:--with-oci8=shared,instantclient,%{_libdir}/oracle/%{oraclever}/client64/lib,%{oraclever}} \
%else
@@ -725,7 +736,11 @@
       --enable-fastcgi \
       --enable-pdo=shared \
       --with-pdo-odbc=shared,unixODBC,%{_prefix} \
+%if %{with_mysqlnd}
+      --with-pdo-mysql=shared,mysqlnd \
+%else
       --with-pdo-mysql=shared,%{_prefix} \
+%endif
       --with-pdo-pgsql=shared,%{_prefix} \
       --with-pdo-sqlite=shared,%{_prefix} \
       --with-pdo-dblib=shared,%{_prefix} \
@@ -756,6 +771,16 @@
       --with-recode=shared,%{_prefix}
popd

+%if %{with_mysqlnd}
+without_shared="--without-gd \
+      --disable-dom --disable-dba --without-unixODBC \
+      --disable-xmlreader --disable-xmlwriter \
+      --without-sqlite \
+      --disable-phar --disable-fileinfo \
+      --disable-json --without-pspell --disable-wddx \
+      --without-curl --disable-posix \
+      --disable-sysvmsg --disable-sysvshm --disable-sysvsem"
+%else
without_shared="--without-mysql --without-gd \
       --disable-dom --disable-dba --without-unixODBC \
       --disable-pdo --disable-xmlreader --disable-xmlwriter \
@@ -764,6 +789,7 @@
       --disable-json --without-pspell --disable-wddx \
       --without-curl --disable-posix \
       --disable-sysvmsg --disable-sysvshm --disable-sysvsem"
+%endif

# Build Apache module, and the CLI SAPI, /usr/bin/php
pushd build-apache

3. Package with:
   

   rpmbuild -bb --with mysqlnd --define "rhel 5" SPECS/php-5.3.2-remi.spec
   

4. To install via yum, change to the directory where rpms are located and recreate the repodata via:
   
   createrepo .
5. Note: you may need to install some of the missing dependent devel packages. I used a combination of base, epel and remi repository to install the dependencies.

PhpMyAdmin Error:

There are various reasons for this error. Several things to check are:

1. Edit config.inc.php file, and specify '127.0.0.1' (instead of any other value like 'localhost) for the $cfg['Servers'][$i]['host'] setting as follows:
   $cfg['Servers'][$i]['host'] = '127.0.0.1';

2. Check my.cnf file for the following entries:
   [mysqld]
socket=/tmp/mysql.sock
old-passwords

[client]
socket=/tmp/mysql.sock

   Only add the 'old-passwords' mentioned above if you are using an older version of PHP (PHP4).

3. Make sure that you do not have any older versions of MySQL installed that are conflicting.
   
4. Edit config.inc.php file, and specify the MySQL socket location (as seen in the above [mysqld] socket setting):

   For example, if your MySQL socket is /tmp/mysql.sock, find the line in config.inc.php that has the $cfg['Servers'][$i]['socket'] setting and change it to:
   

   $cfg['Servers'][$i]['socket'] = '/tmp/mysql.sock';

5. Set the connection mode to socket by changing $cfg['Servers'][$i]['connect_type'] to:
   
   $cfg['Servers'][$i]['connect_type'] = 'socket';

   If there is no such setting, simply add in the above line and re-try.

6. Add this entry to php.ini file (again changing the '/tmp/mysql.sock' to your actual socket location) and restart Apache:

   mysql.default_socket = "/tmp/mysql.sock"

7. Edit the config.inc.php using the phpmyadmin /setup configuration page:

   In the setup screen, select the end-of-line character to be '\r' if you have a Macintosh, or '\n' for a Linux machine.

8. Disable SELinux if you are using it, and re-try. If it works, SELinux is the issue.
   
9. Run the following at your mysql prompt (using the standard mysql client):
   
   mysql> SELECT USER,HOST FROM mysql.user;

   Next, identify the user your are using for phpMyAdmin, and then run:
   

   mysql> SHOW GRANTS FOR 'user'@'host';

   Ensure that this userid has appropriate access rights.