Sunday, June 30, 2013

MySQL 5.6 on Solaris Using a Solaris PKG

The MySQL.com site does a good job with the documentation on installing MySQL on Solaris. The hyperlinks can be found below.

I create this post as a reference and an example of how it done as well.
(I prefer the bash shell but that is just me... )

bash-3.2# groupadd mysql
bash-3.2# useradd -g mysql mysql
bash-3.2#  pkgadd -d mysql-5.6.12-solaris*.pkg

The following packages are available:
  1  mysql     MySQL Community Server (GPL)
              5.6.12

Select package(s) you wish to process (or 'all' to process
all packages). (default: all) [?,??,q]: all


Now you can take a little break. Solaris takes a little bit ot process and install here.


 Processing package instance <mysql> from </export/home/mysql-5.6.12-solaris10*.pkg>

MySQL Community Server (GPL) 5.6.12
Copyright (c) 2000, , Oracle and/or its affiliates. All rights reserved.
Use is subject to license terms.
Using </opt/mysql> as the package base directory.
## Processing package information.
## Processing system information.
   1 package pathname is already properly installed.
## Verifying disk space requirements.
## Checking for conflicts with packages already installed.
## Checking for setuid/setgid programs.

This package contains scripts which will be executed with super-user
permission during the process of installing this package.

The selected base directory </opt/mysql> must exist before
installation is attempted.

Do you want this directory created now [y,n,?,q] y
Using </opt/mysql> as the package base directory.
## Processing package information.
## Processing system information.
## Verifying disk space requirements.
## Checking for conflicts with packages already installed.
## Checking for setuid/setgid programs.

This package contains scripts which will be executed with super-user
permission during the process of installing this package.

Do you want to continue with the installation of <mysql> [y,n,?] y


Installing MySQL Community Server (GPL) as <mysql>
.....


Again let it do some work...


......2013-06-30 17:40:12 1117 [Note] InnoDB: Starting shutdown...
2013-06-30 17:40:12 1117 [Note] InnoDB: Shutdown completed; log sequence number 1625987

A RANDOM PASSWORD HAS BEEN SET FOR THE MySQL root USER !
You will find that password in '/.mysql_secret'.

You must change that password on your first connect,
no other statement but 'SET PASSWORD' will be accepted.
See the manual for the semantics of the 'password expired' flag.

Also, the account for the anonymous user has been removed.

In addition, you can run:

  /opt/mysql/mysql/bin/mysql_secure_installation

which will also give you the option of removing the test database.
This is strongly recommended for production servers.

See the manual for more instructions.

Please report any problems with the /opt/mysql/mysql/bin/mysqlbug script!

The latest information about MySQL is available on the web at

  http://www.mysql.com

Support MySQL by buying support/licenses at http://shop.mysql.com

New default config file was created as /opt/mysql/mysql/my.cnf and
will be used by default by the server when you start it.
You may edit this file to change server settings


Installation of <mysql> was successful.



So now following the instructins per dev.mysql.com 

bash-3.2# ln /etc/init.d/mysql /etc/rc3.d/S91mysql
bash-3.2# ln /etc/init.d/mysql /etc/rc0.d/K02mysql


So now if I want to use the database I must get past the root password that was dynamically created 1st. I personally like this feature.


#  /etc/init.d/mysql start
Starting MySQL
........ SUCCESS!
bash-3.2# ls -al /.mysql_secret
-rw-------   1 root     other         96 Jun 30 17:40 /.mysql_secret
# more /.mysql_secret
# The random password set for the root user at Sun Jun 30 17:40:07 2013 (local time): c29IHIZ4


So let us log in and change the password then we are ready to get started. Frankly you have no other choice.


bash-3.2# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.12

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


mysql> show grants for 'root'@'localhost';
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement

mysql> SET PASSWORD FOR  'root'@'localhost' = PASSWORD('somepassword');
Query OK, 0 rows affected (0.04 sec)


And that is it. We are now good and ready to go...


mysql> exit
Bye
bash-3.2# rm -f /.mysql_secret
bash-3.2# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.12 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.6.12    |
+-----------+
1 row in set (0.00 sec)



References || Helpful Links:









Sunday, June 16, 2013

Backup and Recovery Script for MySQL using Percona's innobackup and Xtrabackup

So Percona has the widely used backup tool Xtrabackup and they realize that everyone often uses this tool in a script of some kind. The have a page that talks about this:
http://www.percona.com/doc/percona-xtrabackup/2.1/xtrabackup_bin/scripting_backups_xbk.html?id=percona-xtrabackup:xtrabackup:scripting

Since I recently gave an example of how to use the backup in a previous post. I figured I might as well write a script that shows how to script the backup process. Plus it has been years since I wrote in Python so I wanted to get a little practice as well.

So the introduction to the code is below but I have placed the script on github.
It needs more testing but feel free to check the codebase out and update and edit.

Once the code is tested more I can update examples but I wanted to be open with this project from the start.

Since it is in the early stages I would recommend using the --showcommands=1 option so you can see what the code plans to do and maybe try those commands. Obviously it should not be used on a production system yet.


First an introduction to it:

# ./backup_restore.py --help
Usage: backup_restore.py  --process=[fullbackup,incremental,prepare,restore] --help --version --showcommands=1

This program enables you to backup full and incremental backups then prepare
and restore them using Percona's Xtrabackup

Options:
  --version             show program's version number and exit
  -h, --help            show this help message and exit
  --process=PROCESS     What would you like to do --process=
                        [fullbackup,incremental,prepare,restore]
  --debug=DEBUG         TURN DEBUG ON 1 OR OFF 0 OR VERBOSE 3
  --showcommands=SHOWCOMMANDS
                        Shows the commands instead of executing them except
                        for the restore section because we go through that
                        step by step
  --backup_root_directory=BACKUP_ROOT_DIRECTORY
                        THE ROOT DIRECTORY OF ALL YOUR BACKUPS, You can set
                        DEFAULT at start of the script
  --percona_xtrabackup_location=PERCONA_XTRABACKUP_LOCATION
                        THE LOCATION OF YOUR xtrabackup FILE, You can set
                        DEFAULT at start of the script
  --datadir=DATADIR     MYSQL DATA DIR LOCATION, You can set DEFAULT at start
                        of the script
  --username=DB_USERNAME
                        MySQL Username, You can set DEFAULT at start of the
                        script
  --password=DB_PASSWORD
                        MySQL Password, You can set DEFAULT at start of the
                        script
  --default_file=DEFAULT_FILE
                        MySQL my.cnf file location, You can set DEFAULT at
                        start of the script
  --options=PERCONA_OPTIONS
                        Additional Options for innobackupex




Friday, June 14, 2013

max_binlog_cache_size

As you evaluate your database performance and stability it is very likely that you will start to review your variables.

At a glance the typical first reaction to the variables below is.. WAIT something is wrong my box does not have that much RAM or even disk space to meet that MAX limits listed below....

MariaDB [(none)]> select @@max_write_lock_count, @@max_binlog_cache_size, @@max_seeks_for_key, @@myisam_max_sort_file_size\G
*************************** 1. row ***************************
     @@max_write_lock_count: 4294967295                -- 4GB
    @@max_binlog_cache_size: 1844674407370954752      --1.6EB
        @@max_seeks_for_key: 429496729                 -- 4GB
@@myisam_max_sort_file_size: 9223372036853727232        --8EB


You are not alone in concerns with these variables as a few bugs have been listed about these variables over the years. Below are just a few of some legacy ones.


"MySQL currently cannot work with binary log positions greater than 4GB."
Keep in mind that these are just the DEFAULT and MAX settings. You can adjust them to make you feel more comfortable.

MariaDB [(none)]> SET GLOBAL max_binlog_cache_size = 4294967296;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SELECT @@max_binlog_cache_size;
+-------------------------+
| @@max_binlog_cache_size |
+-------------------------+
|              4294967296 |  -- 4GB
+-------------------------+
1 row in set (0.00 sec)


Why you would want to... That is an entirely different topic. This is just the upper limit allowed and transactions get split into 4GB anyway. "The maximum recommended value is 4GB", so you can update it if you so choose too.

Read more about your options with this in the MySQL Documentation:
http://dev.mysql.com/doc/refman/5.5/en/replication-options-binary-log.html#sysvar_max_binlog_cache_size

Thursday, June 13, 2013

MariaDB 10.0.3 Alpha install on Fedora 17 x86_64

MariaDB 10.0.3 Alpha was just released.
So for those of you that recall my previous MariaDB 5.5 install post, I decided to see how it works with 10.0.3.

I like some of the features that are going into the MariaDB and Percona releases. Even if you are a big supporter of MySQL, when features are available in these releases that are not being back ported into MySQL releases DBAs have to review their options and make a choice.

So the install....

As I said before from the previous post I have this installed. So I will just to an upgrade first.

[root@Fedora64 10]# rpm -qa | grep maria
mariadb-5.5.31-1.fc17.x86_64
mariadb-server-5.5.31-1.fc17.x86_64
mariadb-libs-5.5.31-1.fc17.x86_64
mariadb-bench-5.5.31-1.fc17.x86_64
mariadb-devel-5.5.31-1.fc17.x86_64

So  packages conflicted at the start.

MariaDB-10.0.3-fedora17-x86_64-client.rpm
MariaDB-10.0.3-fedora17-x86_64-common.rpm
MariaDB-10.0.3-fedora17-x86_64-compat.rpm
MariaDB-10.0.3-fedora17-x86_64-connect-engine.rpm
MariaDB-10.0.3-fedora17-x86_64-devel.rpm
MariaDB-10.0.3-fedora17-x86_64-server.rpm
MariaDB-10.0.3-fedora17-x86_64-shared.rpm
MariaDB-10.0.3-fedora17-x86_64-test.rpm
 

[root@Fedora64 10]# rpm -Uhv *.rpm
warning: MariaDB-10.0.3-fedora17-x86_64-client.rpm: Header V3 DSA/SHA1 Signature, key ID 1bb943db: NOKEY
error: Failed dependencies:
    libodbc.so.2()(64bit) is needed by MariaDB-connect-engine-10.0.3-1.x86_64
    MySQL-devel conflicts with (installed) mariadb-devel-5.5.31-1.fc17.x86_64 


MariaDB-server-10.0.3-1.x86_64 conflicts with file from package mariadb-server-5.5.31-1.fc17.x86_64 
[root@Fedora64 10]# 


So this is just a Virtualbox instance, for demo and evaluation, so I just removed all that I could and had to uninstall.  I was hopeful that an upgrade would work but this is Alpha code still.
ie:

[root@Fedora64 10]# rpm -e mariadb mariadb-server mariadb-bench
[root@Fedora64 10]# rpm -e mariadb-libs perl-DBD-MySQL percona-xtrabackup


So now that the past is cleared out... 

[root@Fedora64 10]# rpm -ihv *.rpm
Preparing...                ########################################### [100%]
   1:MariaDB-common         ########################################### [ 11%]
   2:MariaDB-server         ########################################### [ 22%]
   3:MariaDB-cassandra-engin########################################### [ 33%]
   4:MariaDB-client         ########################################### [ 44%]
   5:MariaDB-devel          ########################################### [ 56%]
   6:MariaDB-shared         ########################################### [ 67%]
   7:MariaDB-test           ########################################### [ 78%]
   8:MariaDB-compat         ########################################### [ 89%]
   9:galera                 ########################################### [100%]


If you recall the past post, I got the init.d script this time..

[root@Fedora64 10]# /etc/init.d/mysql start
Starting MySQL..... SUCCESS!
[root@Fedora64 10]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 10.0.3-MariaDB MariaDB Server


If they are not terrible on performance then I do not see why these are not on by default:

vi /etc/my.cnf
[mysqld]

userstat=1
# http://www.percona.com/doc/percona-server/5.5/diagnostics/user_stats.html?id=percona-server:features:userstatv2 
# https://kb.askmonty.org/en/user-statistics/  
feedback=ON
# https://kb.askmonty.org/en/user-feedback-plugin/
MariaDB [(none)]> show variables like '%feedback%';
+--------------------------+------------------------------------------+
| Variable_name            | Value                                    |
+--------------------------+------------------------------------------+
| feedback_send_retry_wait | 60                                       |
| feedback_send_timeout    | 60                                       |
...
| feedback_url             | https://mariadb.org/feedback_plugin/post |
| feedback_user_info       |                                          |
+--------------------------+------------------------------------------+

MariaDB [(none)]> show variables like '%userstat%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| userstat      | ON    |
+---------------+-------+




Issues I found 30seconds after install...:

MariaDB [(none)]> show variables;
ERROR 1946 (HY000): Failed to load replication slave GTID position from table mysql.gtid_slave_pos 


That is it so far... I have it installed and can review now...

UPDATE:
I have submitted this as a bug. The MariaDB team got right back to me and pointed out that I failed to run mysql_upgrade and restart. That fixed the Error listed above. Still variables feels like it should show everything it has but this is a valid fix and mistake on my part. Thank you to the MariaDB team.


To learn more:


Wednesday, June 12, 2013

MySQL < 5.5 replication to MySQL 5.6

After hours of frustration..... I will put it simply as do not upgrade to MySQL 5.6 if you are running any  version less that MySQL 5.5.

You have to upgrade to MySQL 5.5 first to keep your sanity and data in tact.

Lots of blog posts and information are available about the password changes in MySQL 5.6 and I support them. I even updated the MySQL 5.6 passwords and the box was up and running just fine. The problem was replication. I had to replicate from a MySQL version less than MySQL 5.5 and it simply would not run. I disabled secure_auth and could connect but still no luck with replication.

While I support replication as an upgrade path, take the time and stick with MySQL 5.5 first.

I ended up downgrading to MySQL 5.5 and everything runs just fine now. If you have to downgrade your MySQL version I had to follow many of the same steps I did in my Maria diaster post to get the box back up. 

Sunday, June 9, 2013

Percona Xtrabackup/innobackupex Backup and Restore process

This is a very simple example of how to use Percona Xtrabackup/innobackupex

This MariaDB just has the World Database in it as an example data.
This all could be scripted but for now it is for demo purposes.

Create a Full backup:
MariaDB [(none)]> create database Start_Of_Demo; -- Just here for the demo
Query OK, 1 row affected (0.00 sec)


[root@Fedora64 src]# innobackupex  --no-lock --parallel=4  --user=root  --extra-lsndir=/usr/local/src/incremental_last_checkpoint/  --no-timestamp /usr/local/src/fullbackup/

xtrabackup: Transaction log of lsn (1597964) to (1597964) was copied.

innobackupex: Backup created in directory '/usr/local/src/fullbackup'
130609 15:41:39  innobackupex: Connection to database server closed
130609 15:41:39  innobackupex: completed OK!

[root@Fedora64 src]# ls -al fullbackup/
total 18472
drwxr-xr-x. 6 root root     4096 Jun  9 15:41 .
drwxr-xr-x. 6 root root     4096 Jun  9 15:49 ..
-rw-r--r--. 1 root root      260 Jun  9 15:41 backup-my.cnf
-rw-r-----. 1 root root 18874368 Jun  9 15:41 ibdata1
drwxr-xr-x. 2 root root     4096 Jun  9 15:41 mysql
drwxr-xr-x. 2 root root     4096 Jun  9 15:41 performance_schema
drwxr-xr-x. 2 root root     4096 Jun  9 15:41 Start_Of_Demo
drwxr-xr-x. 2 root root     4096 Jun  9 15:41 world
-rw-r--r--. 1 root root       13 Jun  9 15:41 xtrabackup_binary
-rw-r-----. 1 root root       89 Jun  9 15:41 xtrabackup_checkpoints
-rw-r-----. 1 root root     2560 Jun  9 15:41 xtrabackup_logfile


Create an incremental backup:

MariaDB [(none)]> create database incremental_1;  -- Just here for the demo
Query OK, 1 row affected (0.00 sec)

[root@Fedora64 src]#innobackupex  --incremental --no-lock --parallel=4  --no-timestamp --user=root  --incremental-basedir=/usr/local/src/incremental_last_checkpoint/ --extra-lsndir=/usr/local/src/incremental_last_checkpoint/ /usr/local/src/incremental/

xtrabackup: Transaction log of lsn (1597964) to (1597964) was copied.

innobackupex: Backup created in directory '/usr/local/src/incremental'
130609 15:47:20  innobackupex: Connection to database server closed
130609 15:47:20  innobackupex: completed OK!

[root@Fedora64 src]# ls -al incremental
total 64
drwxr-xr-x. 7 root root  4096 Jun  9 15:47 .
drwxr-xr-x. 6 root root  4096 Jun  9 15:49 ..
-rw-r--r--. 1 root root   260 Jun  9 15:47 backup-my.cnf
-rw-r-----. 1 root root 16384 Jun  9 15:47 ibdata1.delta
-rw-r-----. 1 root root    44 Jun  9 15:47 ibdata1.meta
drwxr-xr-x. 2 root root  4096 Jun  9 15:47 incremental_1
drwxr-xr-x. 2 root root  4096 Jun  9 15:47 mysql
drwxr-xr-x. 2 root root  4096 Jun  9 15:47 performance_schema
drwxr-xr-x. 2 root root  4096 Jun  9 15:47 Start_Of_Demo
drwxr-xr-x. 2 root root  4096 Jun  9 15:47 world
-rw-r--r--. 1 root root    13 Jun  9 15:47 xtrabackup_binary
-rw-r-----. 1 root root    93 Jun  9 15:47 xtrabackup_checkpoints
-rw-r-----. 1 root root  2560 Jun  9 15:47 xtrabackup_logfile


Create another incremental backup:

MariaDB [(none)]> create database incremental_2;-- Just here for the demo
Query OK, 1 row affected (0.00 sec)

[root@Fedora64 src]# innobackupex  --incremental --no-lock --parallel=4  --no-timestamp --user=root  --incremental-basedir=/usr/local/src/incremental_last_checkpoint/ --extra-lsndir=/usr/local/src/incremental_last_checkpoint/ /usr/local/src/incremental_2/

xtrabackup: Transaction log of lsn (1597964) to (1597964) was copied.

innobackupex: Backup created in directory '/usr/local/src/incremental_2'
130609 15:49:49  innobackupex: Connection to database server closed
130609 15:49:49  innobackupex: completed OK!
[root@Fedora64 src]# ls -al incremental_2
total 68
drwxr-xr-x. 8 root root  4096 Jun  9 15:49 .
drwxr-xr-x. 6 root root  4096 Jun  9 15:49 ..
-rw-r--r--. 1 root root   260 Jun  9 15:49 backup-my.cnf
-rw-r-----. 1 root root 16384 Jun  9 15:49 ibdata1.delta
-rw-r-----. 1 root root    44 Jun  9 15:49 ibdata1.meta
drwxr-xr-x. 2 root root  4096 Jun  9 15:49 incremental_1
drwxr-xr-x. 2 root root  4096 Jun  9 15:49 incremental_2
drwxr-xr-x. 2 root root  4096 Jun  9 15:49 mysql
drwxr-xr-x. 2 root root  4096 Jun  9 15:49 performance_schema
drwxr-xr-x. 2 root root  4096 Jun  9 15:49 Start_Of_Demo
drwxr-xr-x. 2 root root  4096 Jun  9 15:49 world
-rw-r--r--. 1 root root    13 Jun  9 15:49 xtrabackup_binary
-rw-r-----. 1 root root    93 Jun  9 15:49 xtrabackup_checkpoints
-rw-r-----. 1 root root  2560 Jun  9 15:49 xtrabackup_logfile


Now you must keep this in mind.  
  • You database of course has to be shutdown. 
    • If you are doing a restore it is likely it crashed anyway
  • The Data directory must be empty.

Make sure the server is off then clear our the data directory.

[root@Fedora64 src]#  ps -ef | grep mysql
root      4538  1940  0 15:54 pts/2    00:00:00 grep --color=auto mysql

[root@Fedora64 src]# ls -al /var/lib/mysql/
total 28724
drwxr-xr-x.  8 mysql mysql     4096 Jun  9 15:53 .
drwxr-xr-x. 43 root  root      4096 Jun  8 19:41 ..
-rw-rw----.  1 mysql mysql    16384 Jun  9 15:53 aria_log.00000001
-rw-rw----.  1 mysql mysql       52 Jun  9 15:53 aria_log_control
-rw-r--r--.  1 mysql mysql 18874368 Jun  9 15:53 ibdata1
-rw-rw----.  1 mysql mysql  5242880 Jun  9 15:53 ib_logfile0
-rw-rw----.  1 mysql mysql  5242880 Jun  9 15:17 ib_logfile1
drwx------.  2 mysql mysql     4096 Jun  9 15:43 incremental_1
drwx------.  2 mysql mysql     4096 Jun  9 15:48 incremental_2
drwxr-xr-x.  2 mysql mysql     4096 Jun  9 15:16 mysql
drwxr-xr-x.  2 mysql mysql     4096 Jun  9 15:16 performance_schema
drwx------.  2 mysql mysql     4096 Jun  9 15:40 Start_Of_Demo
drwxr-xr-x.  2 mysql mysql     4096 Jun  9 15:16 world

[root@Fedora64 src]# rm -Rf /var/lib/mysql/*

Now you must keep this in mind. When you create your backups and following incremental backups, you will have to restore the full backup first and then apply all incremental backups. So DO NOT think that you can do a full backup an later just restore from the last incremental.  Always keep in mind how many incremental backups you can afford to keep before another full backup is required. 

To restore just the full backup:

innobackupex --copy-back  /usr/local/src/fullbackup/

innobackupex: Starting to copy InnoDB log files
innobackupex: in '/usr/local/src/fullbackup'
innobackupex: back to original InnoDB log directory '/var/lib/mysql'
innobackupex: Finished copying back files.

130609 15:54:57  innobackupex: completed OK!

[root@Fedora64 src]# ls -al /var/lib/mysql/
total 18456
drwxr-xr-x.  6 mysql mysql     4096 Jun  9 15:54 .
drwxr-xr-x. 43 root  root      4096 Jun  8 19:41 ..
-rw-r--r--.  1 root  root  18874368 Jun  9 15:54 ibdata1
drwxr-xr-x.  2 root  root      4096 Jun  9 15:54 mysql
drwxr-xr-x.  2 root  root      4096 Jun  9 15:54 performance_schema
drwxr-xr-x.  2 root  root      4096 Jun  9 15:54 Start_Of_Demo
drwxr-xr-x.  2 root  root      4096 Jun  9 15:54 world


[root@Fedora64 mysql]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 1
Server version: 5.5.31-MariaDB MariaDB Server


That does the full backup but I made incremental backups after that. So it will have to be shutdown and the data directory cleaned out. Why ? You have to apply the incremental backups to the full then restore it.  It is done like the following example shows: 



innobackupex --apply-log --redo-only /usr/local/src/fullbackup/
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
130609 15:57:59  InnoDB: Starting shutdown...
130609 15:58:00  InnoDB: Shutdown completed; log sequence number 1597964
130609 15:58:00  innobackupex: completed OK!

Now let us apply the first incremental directory. You can see in the example below that the incremental_1 directory is now applied in the fullbackup directory. This was not the case earlier. 

innobackupex --apply-log --redo-only /usr/local/src/fullbackup/ --incremental-dir=/usr/local/src/incremental/
130609 15:58:42  innobackupex: completed OK!
[root@Fedora64 src]# ls -al fullbackup/
total 20520
drwxr-xr-x. 7 root root     4096 Jun  9 15:58 .
drwxr-xr-x. 6 root root     4096 Jun  9 15:49 ..
-rw-r--r--. 1 root root      260 Jun  9 15:41 backup-my.cnf
-rw-r-----. 1 root root 18874368 Jun  9 15:58 ibdata1
drwxr-xr-x. 2 root root     4096 Jun  9 15:58 incremental_1
drwxr-xr-x. 2 root root     4096 Jun  9 15:41 mysql
drwxr-xr-x. 2 root root     4096 Jun  9 15:41 performance_schema
drwxr-xr-x. 2 root root     4096 Jun  9 15:41 Start_Of_Demo
drwxr-xr-x. 2 root root     4096 Jun  9 15:41 world
-rw-r--r--. 1 root root       13 Jun  9 15:41 xtrabackup_binary
-rw-r-----. 1 root root       89 Jun  9 15:58 xtrabackup_checkpoints
-rw-r-----. 1 root root  2097152 Jun  9 15:58 xtrabackup_logfile

Now let us apply the second incremental directory. You can see in the example below that the incremental_2 directory is now applied in the fullbackup directory. This was not the case earlier. 
innobackupex --apply-log /usr/local/src/fullbackup/ --incremental-dir=/usr/local/src/incremental_2/
innobackupex: Copying '/usr/local/src/incremental_2/Start_Of_Demo/db.opt' to '/usr/local/src/fullbackup/Start_Of_Demo/db.opt'
130609 16:00:09  innobackupex: completed OK!

[root@Fedora64 src]# ls -al fullbackup/
total 20524
drwxr-xr-x. 8 root root     4096 Jun  9 16:00 .
drwxr-xr-x. 6 root root     4096 Jun  9 15:49 ..
-rw-r--r--. 1 root root      260 Jun  9 15:41 backup-my.cnf
-rw-r-----. 1 root root 18874368 Jun  9 16:00 ibdata1
drwxr-xr-x. 2 root root     4096 Jun  9 15:58 incremental_1
drwxr-xr-x. 2 root root     4096 Jun  9 16:00 incremental_2
drwxr-xr-x. 2 root root     4096 Jun  9 15:41 mysql
drwxr-xr-x. 2 root root     4096 Jun  9 15:41 performance_schema
drwxr-xr-x. 2 root root     4096 Jun  9 15:41 Start_Of_Demo
drwxr-xr-x. 2 root root     4096 Jun  9 15:41 world
-rw-r--r--. 1 root root       13 Jun  9 15:41 xtrabackup_binary
-rw-r-----. 1 root root       89 Jun  9 16:00 xtrabackup_checkpoints
-rw-r-----. 1 root root  2097152 Jun  9 15:58 xtrabackup_logfile


Now let us apply the full backup directory. You can see in the example below that the incremental_2 directory is now applied in the fullbackup directory. This was not the case earlier. 

[root@Fedora64 src]# rm -Rf /var/lib/mysql/*
[root@Fedora64 src]# innobackupex --copy-back  /usr/local/src/fullbackup/
[root@Fedora64 src]# chown -R mysql:mysql /var/lib/mysql

Everything is now restored and available:

[root@Fedora64 mysql]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 1
Server version: 5.5.31-MariaDB MariaDB Server

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| Start_Of_Demo      |
| incremental_1      |
| incremental_2      |
| mysql              |
| performance_schema |
| world              |
+--------------------+

Links available for reference. 


Yum Install MariaDB/MySQL disaster but fixed

So this should be an easy install of MariaDB/MySQL. I do not think this was a Maria issue but just an overall bug. Here is what happened and how I fixed it.

yum install mariadb-server
Then I added the rest to have what you see below.

[root@Fedora64 log]# rpm -qa | grep maria
mariadb-5.5.31-1.fc17.x86_64
mariadb-server-5.5.31-1.fc17.x86_64
mariadb-libs-5.5.31-1.fc17.x86_64
mariadb-devel-5.5.31-1.fc17.x86_64

I thought it was odd that I did not get a /etc/init.d/mysql file but I went with it, I wanted to see what happened.

[root@Fedora64 log]# mysqld_safe
130608 19:54:36 mysqld_safe Logging to '/var/log/mysqld.log'.
130608 19:54:37 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
130608 19:54:39 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

130608 19:54:37 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
130608 19:54:37 InnoDB: The InnoDB memory heap is disabled
130608 19:54:37 InnoDB: Mutexes and rw_locks use GCC atomic builtins
130608 19:54:37 InnoDB: Compressed tables use zlib 1.2.5
130608 19:54:37 InnoDB: Using Linux native AIO
130608 19:54:37 InnoDB: Initializing buffer pool, size = 128.0M
130608 19:54:37 InnoDB: Completed initialization of buffer pool
130608 19:54:37 InnoDB: highest supported file format is Barracuda.
130608 19:54:38  InnoDB: Waiting for the background threads to start
130608 19:54:39 Percona XtraDB (http://www.percona.com) 5.5.31-MariaDB-30.2 started; log sequence number 1597945
130608 19:54:39 [Note] Plugin 'FEEDBACK' is disabled.
130608 19:54:39 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
130608 19:54:39 [Note] Server socket created on IP: '0.0.0.0'.
130608 19:54:39 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist
130608 19:54:39 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

Wow... The first run and a failure is not a good sign. This is a fresh install mysql directory should have been installed. So I started with --skip-grant-tables so I could get into the box and look around.

[root@Fedora64 mysql]# ls -la
total 28700
drwxr-xr-x.  2 mysql mysql     4096 Jun  8 19:58 .
drwxr-xr-x. 43 root  root      4096 Jun  8 19:41 ..
-rw-rw----.  1 mysql mysql    16384 Jun  8 19:50 aria_log.00000001
-rw-rw----.  1 mysql mysql       52 Jun  8 19:50 aria_log_control
-rw-rw----.  1 mysql mysql 18874368 Jun  8 19:50 ibdata1
-rw-rw----.  1 mysql mysql  5242880 Jun  8 19:58 ib_logfile0
-rw-rw----.  1 mysql mysql  5242880 Jun  8 19:45 ib_logfile1
[root@Fedora64 mysql]#

[root@Fedora64 mysql]# mysqld_safe --skip-grant-tables
130608 20:02:45 mysqld_safe Logging to '/var/log/mysqld.log'.
130608 20:02:45 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql


OK so it started... and does run but still a MAJOR problem still no MYSQL table !

[root@Fedora64 /]# mysql_upgrade
Phase 1/3: Fixing table and database names
Phase 2/3: Checking and upgrading tables
Processing databases
information_schema
Phase 3/3: Running 'mysql_fix_privilege_tables'...
ERROR 1049 (42000): Unknown database 'mysql'
FATAL ERROR: Upgrade failed

This can still be fixed....
[root@Fedora64 /]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 5.5.31-MariaDB MariaDB Server

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.01 sec)

MariaDB [(none)]> create database mysql ;
Query OK, 1 row affected (0.13 sec)

MariaDB [(none)]> exit
Bye
OK now it has a mysql table I should be able to upgrade it
[root@Fedora64 /]# mysql_upgrade
Phase 1/3: Fixing table and database names
Phase 2/3: Checking and upgrading tables
Processing databases
information_schema
mysql
Phase 3/3: Running 'mysql_fix_privilege_tables'...
OK
[root@Fedora64 /]#

OK So I stopped mysqld and started it without --skip-grants after all it just installed it and I have not yet set a password.

[root@Fedora64 mysql]# mysqld_safe
[root@Fedora64 /]# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)



Ok so let me try that again...


[root@Fedora64 mysql]# mysqld_safe --skip-grant-tables

MariaDB [mysql]> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| servers                   |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
24 rows in set (0.01 sec)

MariaDB [mysql]> select * from user;
Empty set (0.00 sec)

MariaDB [(none)]>  create user root ;

I cannot use the following command because --skip-grant-tables in enabled.
create user root identified by '';

 So now I have a root user only by name because it has zero privileges.

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

So I have to go into the box again with --skip-grant-tables enabled and update the root account

 UPDATE user
SET Select_priv = 'Y',
  Insert_priv='Y',
           Update_priv='Y',
           Delete_priv='Y',
           Create_priv='Y',
             Drop_priv='Y',
           Reload_priv='Y',
         Shutdown_priv='Y',
          Process_priv='Y',
             File_priv='Y',
            Grant_priv='Y',
       References_priv='Y',
            Index_priv='Y',
            Alter_priv='Y',
          Show_db_priv='Y',
            Super_priv='Y',
 Create_tmp_table_priv='Y',
      Lock_tables_priv='Y',
          Execute_priv='Y',
       Repl_slave_priv='Y',
      Repl_client_priv='Y',
      Create_view_priv='Y',
        Show_view_priv='Y',
   Create_routine_priv='Y',
    Alter_routine_priv='Y',
      Create_user_priv='Y',
            Event_priv='Y',
          Trigger_priv='Y',
Create_tablespace_priv='Y'
WHERE user = 'root';


Now a restart without --skip-grant-tables enabled and I am in as root!

[root@Fedora64 /]# ps -ef | grep mysql
root      4522  1513  0 20:26 pts/0    00:00:00 /bin/sh /bin/mysqld_safe
mysql     4650  4522  0 20:27 pts/0    00:00:03 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock
root      8348  3178  0 20:47 pts/1    00:00:00 grep --color=auto mysql
[root@Fedora64 /]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.31-MariaDB MariaDB Server

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>



WHEW. This is more of an example of how to fix it when things go wrong but I still wanted the /etc/init.d/mysql file




Saturday, June 8, 2013

Pivot table or No Pivot Table

This topic recently came up on the forums.mysql.com site.

The opinion expressed was that pivot tables are very hard to scale and maintain it would be worth a redesign of schema instead of a pivot table. This is a valid opinion with valid points.

I would like to add the topic here to help express my point of view and have it available for others.

It all depends on the data being gathered on if you should use a pivot table or not. The example given in a previous post by me was just an example of how they work.

If you are collecting known  user information (First and Last name, Address information, Phone ) then yes a pivot table is more complicated that what you would need.  If you just have a few data points to tie them to outside of that core information then yes another table is a solution and tied with a simple join.

The pivot table concept is valid when it is for dynamic amounts of data per entity you are collecting.
You might need 10 data points for 100 users. You might need 500 data points on the next 100 users.  Can the schema handle it easily?  

The example given in previous post I agree  does not require a pivot table. But I just used the concept given to me in the forum to answer the question asked.

Ideally you can use both solutions in your schema. Core data points, keep in columns. Dynamic data keep in pivot tables.

If it is built correctly it is very scalable, the billions and billions of data I stored in the pivot table proved this to me easily.  That does not mean it would not require some work. You very well might find that creating some views or summary tables that look into the pivot table would be easier for others to gather data. This begs the question then why wasn't the data stored that way in the first place? Again it depends on the dynamic nature of your data and application that uses the data.

MEMORY and TEMPORARY Tables

Since I have received a request to help answer forum.mysql.com questions with the blog I will continue to post some extended examples here.

I noticed this post: http://forums.mysql.com/read.php?10,588192,588192#msg-588192  and I first thought of a different way to handle the situation.

If you need tables to handle temporary information you can go about it in two ways. One if it is per session processing then you should create a TEMPORARY table only:

 CREATE TEMPORARY TABLE `temporary_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ; 


This will result in NO .frm file and will go away and close of the session.
If you need it available longer and need it to be fast you can use a MEMORY table. This will stay until you reboot database, delete table, etc... 

CREATE TABLE `memory_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=MEMORY;


This again means NO .frm file.

So if you want to go clean the memory tables  up because you have so many or something you can find a list with the following ...
SELECT TABLE_SCHEMA, ENGINE, COUNT(*) AS count_tables,
  SUM(DATA_LENGTH+INDEX_LENGTH) AS size,
SUM(INDEX_LENGTH) AS index_size FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA NOT IN ('mysql', 'INFORMATION_SCHEMA')
  AND ENGINE = "MEMORY" GROUP BY TABLE_SCHEMA, ENGINE;


As always... review your needs and benchmark what works best for you and your application.

Friday, June 7, 2013

Pivot tables example in MySQL

I was asked on the forums.mysql.com site how to build a subscription table to track courses and etc.

It was easier to post the full example here, it is a quick brief example but you get the idea.

The concept here is simple.
We store information in rows that we can then pull back out into different columns when needed.

The request was for a subscription of students and courses....

First I built out some tables and data...


 CREATE TABLE `details` (
  `details_id` int(11) NOT NULL AUTO_INCREMENT,
  `details_label` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`details_id`)
) ENGINE=InnoDB;
INSERT INTO details VALUES (1,'First Name') , (2, 'Last Name') ;

CREATE TABLE `subjects` (
  `subject_id` int(11) NOT NULL AUTO_INCREMENT,
  `subject` enum('History','English','Geography','Mathematics','Science','Social Studies','Foreign Languages','Visual and Performing Arts') DEFAULT NULL,
  `subject_detail` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`subject_id`)
) ENGINE=InnoDB;
INSERT INTO subjects VALUES (1,'Mathematics', 'Algebra') , (2,'History', '1826-1926')  ,  (3,'Geography', ' Africa Studies') ;

CREATE TABLE `student` (
  `student_id` int(11) NOT NULL AUTO_INCREMENT,
  `email` varchar(150) DEFAULT NULL,
  `student_key` varchar(20) DEFAULT NULL,
 `date_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`student_id`)
) ENGINE=InnoDB;
INSERT INTO student (`student_id` ,`email`,`student_key`) VALUES (1,'foobar@gmail.com','iasdjf');

CREATE TABLE `student_details` (
  `student_details_id` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` int(11) DEFAULT 0,
  `details_id` int(11)  DEFAULT 0,
  `details_value` varchar(255) DEFAULT NULL,
  `date_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`student_details_id`)
) ENGINE=InnoDB;
INSERT INTO student_details VALUES (NULL,1,1,'John',NOW()) ,  (NULL,1,2,'Smith',NOW()) ;

 CREATE TABLE `courselist` (
  `courselist_id` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` int(11) DEFAULT 0,
  `subject_id` int(11) DEFAULT NULL,
  `status` enum('Waitlisted','Subscribed','Denied') DEFAULT NULL,
  `date_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`courselist_id`)
) ENGINE=InnoDB;
INSERT INTO courselist VALUES ( NULL,1, 1 , 'Waitlisted' , NOW() ) , ( NULL,1, 2 , 'Subscribed' , NOW() )  , ( NULL,1, 3 , 'Denied' , NOW() )   ;

First just pull information about the student : 


> SELECT s.student_id , d.details_label , sd.details_value
    -> FROM student s
    -> INNER JOIN student_details sd ON s.student_id = sd.student_id
    -> INNER JOIN details d ON sd.details_id = d.details_id;
+------------+---------------+---------------+
| student_id | details_label | details_value |
+------------+---------------+---------------+
|          1 | First Name    | John          |
|          1 | Last Name     | Smith         |
+------------+---------------+---------------+
2 rows in set (0.00 sec)


We can dig more and keep adding information...


> SELECT s.student_id , d.details_label , sd.details_value , c.status, j.subject, j.subject_detail
    -> FROM student s
    -> INNER JOIN student_details sd ON s.student_id = sd.student_id
    -> INNER JOIN details d ON sd.details_id = d.details_id
    -> INNER JOIN courselist c ON s.student_id = c.student_id
    -> INNER JOIN subjects j ON j.subject_id = c.subject_id
    -> ;
+------------+---------------+---------------+------------+-------------+-----------------+
| student_id | details_label | details_value | status     | subject     | subject_detail  |
+------------+---------------+---------------+------------+-------------+-----------------+
|          1 | First Name    | John          | Waitlisted | Mathematics | Algebra         |
|          1 | Last Name     | Smith         | Waitlisted | Mathematics | Algebra         |
|          1 | First Name    | John          | Subscribed | History     | 1826-1926       |
|          1 | Last Name     | Smith         | Subscribed | History     | 1826-1926       |
|          1 | First Name    | John          | Denied     | Geography   |  Africa Studies |
|          1 | Last Name     | Smith         | Denied     | Geography   |  Africa Studies |
+------------+---------------+---------------+------------+-------------+-----------------+
6 rows in set (0.00 sec)



That is not very useful or clean though...
So redo this to pull exactly what we want...


> SELECT s.student_id ,sd1.details_value as FIRST_NAME, sd2.details_value as LAST_NAME,   c.status, j.subject, j.subject_detail
    -> FROM student s
    -> INNER JOIN student_details sd1 ON s.student_id = sd1.student_id AND sd1.details_id = 1
    -> INNER JOIN student_details sd2 ON s.student_id = sd2.student_id AND sd2.details_id = 2
    -> INNER JOIN courselist c ON s.student_id = c.student_id
    -> INNER JOIN subjects j ON j.subject_id = c.subject_id
    -> ;
+------------+------------+-----------+------------+-------------+-----------------+
| student_id | FIRST_NAME | LAST_NAME | status     | subject     | subject_detail  |
+------------+------------+-----------+------------+-------------+-----------------+
|          1 | John       | Smith     | Waitlisted | Mathematics | Algebra         |
|          1 | John       | Smith     | Subscribed | History     | 1826-1926       |
|          1 | John       | Smith     | Denied     | Geography   |  Africa Studies |
+------------+------------+-----------+------------+-------------+-----------------+
3 rows in set (0.00 sec)





Thursday, June 6, 2013

MySQL Check Table

The MySQL Check tables command is very useful for anyone who wants to do the following:
  • Checking Version Compatibility 
  • Checking Data Consistency 
  • Upgrades
  • General Table Errors
 The process is simple enough:

> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| City            |
| Country         |
| CountryLanguage |
+-----------------+

> check table City\G
*************************** 1. row ***************************
   Table: world.City
      Op: check
Msg_type: status
Msg_text: OK


This is a good task to stay updated on so you are aware of possible errors. One possible problem is that this tool really focuses on MyISAM and not InnoDB. If you use it for InnoDB, The "Check table" command really only applies when you add the QUICK option (or no options).  The FAST, CHANGED, MEDIUM and EXTENDED options are all ignored for InnoDB. Now, if you are asking yourself, what about InnoDB? Why would MySQL ignore data consistency in the InnoDB engine? Take a deep breath and relax, InnoDB is ACID complaint, ACID is "an acronym standing for atomicity, consistency, isolation, and durability." So do not disregard checking InnoDB tables because it can still provide you some insight or confirmation on your tables. Keep in mind that if an InnoDB table was to be corrupted the server will shut down to protect the data. You just get more bang for your buck with MyISAM tables and this tool.

Hopefully you get a response of "OK" or "Table is already up to date" otherwise you need to run a repair table to fix the table.

So what are the options available to us so you can do this often and easily.
The documentation link below will also provide you with several community driven automatic options. You can script it the process and easily show tables then apply check tables to all of your results. It just appears easier to me though to use the tools provided for you.


$ mysqlcheck -u root -p --databases world --fast
Enter password:
world.City                               OK
world.Country                            OK
world.CountryLanguage                    OK

$mysqlcheck -u root -p --databases world --fast --check-only-changed
Enter password:
world.City                               OK
world.Country                            OK
world.CountryLanguage                    OK

Now this is simple and direct but it also lends itself to another question, What about the password?

Should you create a user with no password that is allowed to check tables just so you do not have to put a password into your script or cron job? You want to prevent having the password sitting around in .mysql_history files as well.  So again take advantage of the tools available for you. MySQL 5.6 introduced the MySQL Configuration Utility.  I have an example of how to set it up in a previous blog post:
http://anothermysqldba.blogspot.com/2013/05/mysql-users-grants-mysqlconfigeditor.html

mysqlcheck --login-path=local  --databases world --fast --check-only-changed
world.City                              OK
world.Country                           OK
world.CountryLanguage                   OK

$ mysqlcheck --help will provide a full list of options available to you.
Now, you can check all of your tables, keep your passwords out of the crontab file and/or scripts.

Documentation: