MySQL Cluster (NDB) Example
Posted at Sunday, March 30, 2008
Here are my notes on how to set-up a simple MySQL Cluster configuration. The following limits the configuration to 2 Linux Servers. By no means a production set-up, the following is meant to demonstrate the configuration/operational basics:
This example assumes that there are 2 pre-existing hosts, both with binary versions of MySQL 5.0 installed.
Download the applicable Cluster binaries from MySQL
On both hosts
There are certain dependencies that need to dealt with before installing the cluster binaries; you may want to consult the release notes for your applicable version. In my case, I need to update the perl module HTML::Template:
Install the applicable cluster binaries to the existing distribution:
On the host where the Management Server will reside, create the /var/lib/mysql-cluster/config.ini configuration file and edit as appropriate:
config.ini
As you can see, I have configured this file to reflect the aforementioned node locations.
On both hosts
Configure the /etc/my.cnf to reflect the location of the Management Server; this is used by the Data Nodes:
Also, update the option file used to start each sqld process:
At this point, you are ready to start the processes:
On the Management Server:
You can ignore the aforementioned message, this warning occurs because it is customary to run the Management Server on a seperate host from that of the Data/sqld nodes.
On both hosts
Start the Data Nodes and re-start the sqld processes:
Once all processes are started on all Nodes, issue the following on the Management Server's Node:
You should see something similar to the above. Additionally, each sqld process
should show the ndbcluster engine as enabled:
Test the configuration by creating a table on one of the nodes - assigned to the ndbcluster storage engine - in a database that is present in every nodes' datadir; for example:
Insert a sample data row:
Once you begin to use the cluster storage engine, you will be able to monitor its statisics via:
To complete the test, login to the other Node and query its database to see whether the aforementioned table and row now exist there:
As you can see, the changes are now present on the other Node!
The sample configuration is now up and running and ready for further testing, etc.
| Node | Management Server | Data Node | sqld Node |
|---|---|---|---|
| 192.168.1.101 | Y | Y (Group A) | Y |
| 192.168.1.102 | N | Y (Group B) | Y |
This example assumes that there are 2 pre-existing hosts, both with binary versions of MySQL 5.0 installed.
Download the applicable Cluster binaries from MySQL
On both hosts
There are certain dependencies that need to dealt with before installing the cluster binaries; you may want to consult the release notes for your applicable version. In my case, I need to update the perl module HTML::Template:
# cpan 'install HTML::Template'
Install the applicable cluster binaries to the existing distribution:
rpm -ivh --nodeps MySQL-clusterstorage-community-5.0.51a-0.rhel4.i386.rpm \
MySQL-clustermanagement-community-5.0.51a-0.rhel4.i386.rpm \
MySQL-clustertools-community-5.0.51a-0.rhel4.i386.rpm \
MySQL-clusterextra-community-5.0.51a-0.rhel4.i386.rpm
On the host where the Management Server will reside, create the /var/lib/mysql-cluster/config.ini configuration file and edit as appropriate:
config.ini
[ndbd default]
NoOfReplicas=2
DataMemory=80M
IndexMemory=18M
[tcp default]
portnumber=2202
[ndb_mgmd]
hostname=192.168.1.101
datadir=/var/lib/mysql
# Options for data node "A":
[ndbd]
hostname=192.168.1.101
datadir=/var/lib/mysql
# Options for data node "B":
[ndbd]
hostname=192.168.1.102
datadir=/var/lib/mysql
# SQL node options:
[mysqld]
hostname=192.168.1.101
[mysqld]
hostname=192.168.1.102
As you can see, I have configured this file to reflect the aforementioned node locations.
On both hosts
Configure the /etc/my.cnf to reflect the location of the Management Server; this is used by the Data Nodes:
[mysql_cluster]
ndb-connectstring=192.168.1.101
Also, update the option file used to start each sqld process:
[mysqld]
ndbcluster
ndb-connectstring = 192.168.1.101
At this point, you are ready to start the processes:
On the Management Server:
# ndb_mgmd -f /var/lib/mysql-cluster/config.ini
Warning line 29: Cluster configuration warning:
arbitrator with id 1 and db node with id 2 on same host 192.168.1.101
Running arbitrator on the same host as a database node may
cause complete cluster shutdown in case of host failure.
You can ignore the aforementioned message, this warning occurs because it is customary to run the Management Server on a seperate host from that of the Data/sqld nodes.
On both hosts
Start the Data Nodes and re-start the sqld processes:
# ndbd
# service mysql-5.0.51 restart
Shutting down MySQL... [ OK ]
Starting MySQL.......... [ OK ]
Once all processes are started on all Nodes, issue the following on the Management Server's Node:
# ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> show
Connected to Management Server at: 192.168.1.101:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=2 @192.168.1.101 (Version: 5.0.51, Nodegroup: 0, Master)
id=3 @192.168.1.102 (Version: 5.0.51, Nodegroup: 0)
[ndb_mgmd(MGM)] 1 node(s)
id=1 @192.168.1.101 (Version: 5.0.51)
[mysqld(API)] 2 node(s)
id=4 @192.168.1.101 (Version: 5.0.51)
id=5 @192.168.1.102 (Version: 5.0.51)
You should see something similar to the above. Additionally, each sqld process
should show the ndbcluster engine as enabled:
mysql> show storage engines;
+------------+---------+----------------------------------------------------------------+
Engine Support Comment
+------------+---------+----------------------------------------------------------------+
MyISAM DEFAULT Default engine as of MySQL 3.23 with great performance
MEMORY YES Hash based, stored in memory, useful for temporary tables
InnoDB YES Supports transactions, row-level locking, and foreign keys
BerkeleyDB NO Supports transactions and page-level locking
BLACKHOLE YES /dev/null storage engine (anything you write to it disappears)
EXAMPLE YES Example storage engine
ARCHIVE YES Archive storage engine
CSV YES CSV storage engine
ndbcluster YES Clustered, fault-tolerant, memory-based tables
FEDERATED YES Federated MySQL storage engine
MRG_MYISAM YES Collection of identical MyISAM tables
ISAM NO Obsolete storage engine
+------------+---------+----------------------------------------------------------------+
12 rows in set (0.00 sec)
Test the configuration by creating a table on one of the nodes - assigned to the ndbcluster storage engine - in a database that is present in every nodes' datadir; for example:
mysql> use cluster;
Database changed
mysql> CREATE TABLE `cluster_test` (
-> `id` tinyint(4) NOT NULL,
-> PRIMARY KEY (`id`)
-> ) ENGINE=ndbcluster DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.56 sec)
Insert a sample data row:
mysql> insert into cluster_test values (100);
Query OK, 1 row affected (0.01 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
Once you begin to use the cluster storage engine, you will be able to monitor its statisics via:
mysql> show engine ndb status;
+-----------------------+---------+------+--------+
free_list created free sizeof
+-----------------------+---------+------+--------+
NdbTransaction 2 0 208
NdbOperation 4 4 652
NdbIndexScanOperation 1 1 736
NdbIndexOperation 0 0 656
NdbRecAttr 516 516 72
NdbApiSignal 16 16 136
NdbLabel 0 0 196
NdbBranch 0 0 24
NdbSubroutine 0 0 68
NdbCall 0 0 16
NdbBlob 0 0 204
NdbReceiver 2 0 68
+-----------------------+---------+------+--------+
12 rows in set (0.00 sec)
To complete the test, login to the other Node and query its database to see whether the aforementioned table and row now exist there:
mysql> use cluster
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+-------------------+
Tables_in_cluster
+-------------------+
cluster_test
+-------------------+
1 row in set (0.01 sec)
mysql> select * from cluster_test;
+-----+
id
+-----+
100
+-----+
1 row in set (0.00 sec)
mysql> show engine ndb status;
+-----------------------+---------+------+--------+
free_list created free sizeof
+-----------------------+---------+------+--------+
NdbTransaction 3 0 208
NdbOperation 4 4 652
NdbIndexScanOperation 1 1 736
NdbIndexOperation 0 0 656
NdbRecAttr 516 516 72
NdbApiSignal 16 16 136
NdbLabel 0 0 196
NdbBranch 0 0 24
NdbSubroutine 0 0 68
NdbCall 0 0 16
NdbBlob 0 0 204
NdbReceiver 2 0 68
+-----------------------+---------+------+--------+
12 rows in set (0.00 sec)
As you can see, the changes are now present on the other Node!
The sample configuration is now up and running and ready for further testing, etc.
Labels: MySQL
MySQL: Backup and Recovery using innobackup
Posted at Monday, March 17, 2008
It is possible to perform a 'hot' (i.e. online) backup of InnoDB databases using a 3rd-party tool called innobackup from INNOBASE.
Using this tool takes a little getting used to; the following is an example hot backup and recovery:
Request an evaluation copy of the product here. You will receive the evaulation program via email, rename it to ibbackup and place it in your PATH.
Also, download the wrapper script, innobackup, cutting and pasting it into a file called innobackup; this script calls the previously acquired evaluation program, ibbackup; place this in your PATH as well.
I place the programs in the bin directory of MySQL's basedir:
In my example, I start with a fresh MySQL install.
First I create a new database and sample table assigned to the InnoDB storage engine:
For good measure, I flush the logs via mysqladmin:
Now I will take a hot, online backup using the innobackup script:
backup_innodb.bsh
Run the script as mysql:
Examine the resulting log file:
The backup writes to the directory I specified:
Prepare the MySQL installation for the recovery test:
Insert a post-backup value to test rolling forward the InnoDB tablespace:
Flush the logs:
Shutdown the MySQL service:
Before we can restore the hot backup we took, we need to make it consistent via the following:
apply_innodb.bsh
Run the script as mysql:
Examine the resulting log file:
The aforementioned, makes the InnoDB tablespace in question consistent to change #536.
Now the backup is ready to copy back:
restore_innodb.bsh
Run the script as mysql:
Examine the resulting log file:
The backup has now been restored to the datadir.
Before starting the server, prepare the changes from the binlogs:
Obtain the starting change position from the following file:
We will roll-forward starting from here. Stage binlogs changes as appropriate; for example:
Starting from the previously identified binlog and position:
At this point, all the changes to roll the databases forward are contained in the
updates2apply file we have created.
Start the MySQL service:
Query the sample table; notice how only the changes at the time of the backup are present:
Roll forward the databases via:
You will notice there was an error, this is due to a bug that creates an invalid SQL statement at line 17. Because of bugs in the mysqlbinlog program, be sure to use the --force option whenever rolling forward your databases.
Query the sample table; notice how changes applied after the backup are now present:
The innobackup tool gives you plenty of other options, such as starting MySQL directly from backups' directories (instead of restoring them to their original location).
Using this tool takes a little getting used to; the following is an example hot backup and recovery:
Request an evaluation copy of the product here. You will receive the evaulation program via email, rename it to ibbackup and place it in your PATH.
Also, download the wrapper script, innobackup, cutting and pasting it into a file called innobackup; this script calls the previously acquired evaluation program, ibbackup; place this in your PATH as well.
I place the programs in the bin directory of MySQL's basedir:
# ls -lart /opt/mysql/5.1.23/bin/*backup
-rwxrwx--- 1 mysql mysql 50918 Mar 16 15:59 innobackup
-rwxrwx--- 1 mysql mysql 1002450 Mar 16 16:09 ibbackup
In my example, I start with a fresh MySQL install.
First I create a new database and sample table assigned to the InnoDB storage engine:
# mysql -u root --password=password -P 3307 -h localhost --protocol=tcp -p
mysql> create database innodbsmpl;
Query OK, 1 row affected (0.00 sec)
mysql> use innodbsmpl;
Database changed
mysql> create table innodbsmpl (sampleval varchar(200)) engine=InnoDB;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into innodbsmpl values ('Before Backup');
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
For good measure, I flush the logs via mysqladmin:
# mysqladmin -u root --password=password -P 3307 -h localhost --protocol=tcp -p flush-log
Now I will take a hot, online backup using the innobackup script:
backup_innodb.bsh
# more backup_innodb.bsh
#/bin/bash
export PATH=/opt/mysql/5.1.23/bin:$PATH;
/opt/mysql/5.1.23/bin/innobackup --no-timestamp --socket=/opt/mysql/5.1.23/socke
t/mysql-5123.sock --user root --password=password /opt/mysql/5.1.23/my.cnf /tmp/
innodbsmpl_backup > backup_innodb.log 2>&1
Run the script as mysql:
# ./backup_innodb.bsh
Examine the resulting log file:
# more backup_innodb.log
InnoDB Backup Utility v1.4.0; Copyright 2003-2007 Innobase Oy
This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.
IMPORTANT: Please check that the backup run completes successfully.
At the end of a successful backup run innobackup
prints "innobackup completed OK!".
innobackup: Using mysql Ver 14.14 Distrib 5.1.23-rc, for redhat-linux-gnu (i686) using
EditLine wrapper
innobackup: Using mysql server version 5.1.23-rc-log
innobackup: Using InnoDB Hot Backup version 3.0.0
innobackup: Created backup directory /tmp/innodbsmpl_backup
080317 12:20:03 innobackup: Starting mysql with options: --unbuffered --password=passw
ord --user=root --socket=/opt/mysql/5.1.23/socket/mysql-5123.sock
080317 12:20:03 innobackup: Connected to database with mysql child process (pid=12220)
080317 12:20:07 innobackup: Connection to database server closed
080317 12:20:07 innobackup: Starting ibbackup with command: ibbackup --suspend-at-end
/opt/mysql/5.1.23/my.cnf /tmp/innodbsmpl_backup/backup-my.cnf
innobackup: Waiting for ibbackup (pid=12227) to suspend
innobackup: Suspend file '/tmp/innodbsmpl_backup/ibbackup_suspended'
InnoDB Hot Backup version 3.0.0; Copyright 2002-2005 Innobase Oy
License A11900 is granted to James Colestock (james@colestock.com)
(--apply-log works in any computer regardless of the hostname)
Licensed for use in a computer whose hostname is 'rac2.colestock.test'
Expires 2008-5-1 (year-month-day) at 00:00
See http://www.innodb.com for further information
Type ibbackup --license for detailed license terms, --help for help
Contents of /opt/mysql/5.1.23/my.cnf:
innodb_data_home_dir got value /opt/mysql/datafiles
innodb_data_file_path got value ibdata1:10M:autoextend
datadir got value /opt/mysql/datafiles
innodb_log_group_home_dir got value /opt/mysql/datafiles
innodb_log_files_in_group got value 2
innodb_log_file_size got value 5242880
Contents of /tmp/innodbsmpl_backup/backup-my.cnf:
innodb_data_home_dir got value /tmp/innodbsmpl_backup
innodb_data_file_path got value ibdata1:10M:autoextend
datadir got value /tmp/innodbsmpl_backup
innodb_log_group_home_dir got value /tmp/innodbsmpl_backup
innodb_log_files_in_group got value 2
innodb_log_file_size got value 5242880
ibbackup: Found checkpoint at lsn 0 48301
ibbackup: Starting log scan from lsn 0 48128
080317 12:20:07 ibbackup: Copying log...
080317 12:20:07 ibbackup: Log copied, lsn 0 48301
ibbackup: We wait 1 second before starting copying the data files...
080317 12:20:08 ibbackup: Copying /opt/mysql/datafiles/ibdata1
ibbackup: You had specified the option --suspend-at-end
080317 12:20:09 innobackup: Continuing after ibbackup has suspended
080317 12:20:09 innobackup: Starting mysql with options: --unbuffered --password=passw
ord --user=root --socket=/opt/mysql/5.1.23/socket/mysql-5123.sock
080317 12:20:09 innobackup: Connected to database with mysql child process (pid=12228)
080317 12:20:08 ibbackup: Suspending the backup procedure to wait
ibbackup: until you delete the marker file /tmp/innodbsmpl_backup/ibbackup_suspended
080317 12:20:13 innobackup: Starting to lock all tables...
080317 12:20:29 innobackup: All tables locked and flushed to disk
080317 12:20:29 innobackup: Starting to backup .frm, .MRG, .MYD, .MYI,
innobackup: .TRG, .TRN, and .opt files in
innobackup: subdirectories of '/opt/mysql/datafiles'
innobackup: Backing up files '/opt/mysql/datafiles/mysql/*.{frm,MYD,MYI,MRG,TRG,TRN,opt
}' (70 files)
innobackup: Backing up file '/opt/mysql/datafiles/innodbsmpl/innodbsmpl.frm'
innobackup: Backing up file '/opt/mysql/datafiles/innodbsmpl/db.opt'
innobackup: Backing up file '/opt/mysql/datafiles/test/test.frm'
innobackup: Backing up file '/opt/mysql/datafiles/test/test.MYD'
innobackup: Backing up file '/opt/mysql/datafiles/test/test.MYI'
innobackup: Backing up file '/opt/mysql/datafiles/test/db.opt'
080317 12:20:29 innobackup: Finished backing up .frm, .MRG, .MYD, .MYI, .TRG, .TRN, an
d .opt files
innobackup: Resuming ibbackup
080317 12:20:30 ibbackup: Suspension ends. Continuing the backup procedure.
080317 12:20:30 ibbackup: Copying of the last data file is close to ending...
ibbackup: We still once copy the latest flushed log to ibbackup_logfile.
ibbackup: A copied database page was modified at 0 48301
ibbackup: Scanned log up to lsn 0 50133
ibbackup: Was able to parse the log up to lsn 0 50133
ibbackup: Maximum page number for a log record 51
080317 12:20:30 ibbackup: Full backup completed!
080317 12:20:34 innobackup: All tables unlocked
080317 12:20:34 innobackup: Connection to database server closed
innobackup: Backup created in directory '/tmp/innodbsmpl_backup'
innobackup: MySQL binlog position: filename 'rac2_bin.000002', position 536 -
080317 12:20:34 innobackup: innobackup completed OK!
The backup writes to the directory I specified:
# ls -lart /tmp/innodbsmpl_backup/
total 10304
-rw-r--r-- 1 mysql mysql 305 Mar 17 12:20 backup-my.cnf
drwxrwxrwt 11 root root 12288 Mar 17 12:20 ..
-rw-r----- 1 mysql mysql 10485760 Mar 17 12:20 ibdata1
-rw-r--r-- 1 mysql mysql 0 Mar 17 12:20 mysql-stderr
drwxr-xr-x 2 mysql mysql 4096 Mar 17 12:20 test
drwxr-xr-x 2 mysql mysql 4096 Mar 17 12:20 mysql
drwxr-xr-x 2 mysql mysql 4096 Mar 17 12:20 innodbsmpl
-rw-r--r-- 1 mysql mysql 22 Mar 17 12:20 ibbackup_binlog_info
drwxr-xr-x 5 mysql mysql 4096 Mar 17 12:20 .
-rw-r----- 1 mysql mysql 2560 Mar 17 12:20 ibbackup_logfile
-rw-r--r-- 1 mysql mysql 532 Mar 17 12:20 mysql-stdout
Prepare the MySQL installation for the recovery test:
Insert a post-backup value to test rolling forward the InnoDB tablespace:
# mysql -u root -P 3307 -h localhost --protocol=tcp -p
mysql> use innodbsmpl
Database changed
mysql> insert into innodbsmpl values ('After Backup');
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
Flush the logs:
# /opt/mysql/5.1.23/bin/mysqladmin -u root -P 3307 --protocol=tcp -p flush-logs
Shutdown the MySQL service:
# su - root
# service mysql-5.1.23 stop
Shutting down MySQL. [ OK ]
Before we can restore the hot backup we took, we need to make it consistent via the following:
apply_innodb.bsh
# more apply_innodb.bsh
#/bin/bash
export PATH=/opt/mysql/5.1.23/bin:$PATH;
/opt/mysql/5.1.23/bin/innobackup --apply-log /tmp/innodbsmpl_backup/backup-my.cnf /tmp/
innodbsmpl_backup > apply_innodb.log 2>&1
Run the script as mysql:
# ./apply_innodb.bsh
Examine the resulting log file:
# more apply_innodb.log
InnoDB Backup Utility v1.4.0; Copyright 2003-2007 Innobase Oy
This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.
IMPORTANT: Please check that the apply-log run completes successfully.
At the end of a successful apply-log run innobackup
prints "innobackup completed OK!".
innobackup: Using InnoDB Hot Backup version 3.0.0
080317 12:32:15 innobackup: Starting ibbackup with command: ibbackup --restore /tmp/in
nodbsmpl_backup/backup-my.cnf
InnoDB Hot Backup version 3.0.0; Copyright 2002-2005 Innobase Oy
License A11900 is granted to James Colestock (james@colestock.com)
(--apply-log works in any computer regardless of the hostname)
Licensed for use in a computer whose hostname is 'rac2.colestock.test'
Expires 2008-5-1 (year-month-day) at 00:00
See http://www.innodb.com for further information
Type ibbackup --license for detailed license terms, --help for help
Contents of /tmp/innodbsmpl_backup/backup-my.cnf:
innodb_data_home_dir got value /tmp/innodbsmpl_backup
innodb_data_file_path got value ibdata1:10M:autoextend
datadir got value /tmp/innodbsmpl_backup
innodb_log_group_home_dir got value /tmp/innodbsmpl_backup
innodb_log_files_in_group got value 2
innodb_log_file_size got value 5242880
080317 12:32:15 ibbackup: ibbackup_logfile's creation parameters:
ibbackup: start lsn 0 48128, end lsn 0 50133,
ibbackup: start checkpoint 0 48301
InnoDB: Doing recovery: scanned up to log sequence number 0 50133
InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 2
2 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 5
1 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 8
0 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 Setting log file size to 0 5
242880
Setting log file size to 0 5242880
ibbackup: We were able to parse ibbackup_logfile up to
ibbackup: lsn 0 50133
ibbackup: Last MySQL binlog file position 0 536, file name ./rac2_bin.000002
ibbackup: The first data file is '/tmp/innodbsmpl_backup/ibdata1'
ibbackup: and the new created log files are at '/tmp/innodbsmpl_backup/'
080317 12:32:15 ibbackup: Full backup prepared for recovery successfully!
080317 12:32:15 innobackup: innobackup completed OK!
The aforementioned, makes the InnoDB tablespace in question consistent to change #536.
Now the backup is ready to copy back:
restore_innodb.bsh
# more restore_innodb.bsh
#/bin/bash
export PATH=/opt/mysql/5.1.23/bin:$PATH;
/opt/mysql/5.1.23/bin/innobackup --copy-back /opt/mysql/5.1.23/my.cnf /tmp/innodbsmpl_backup > restore_innodb.log 2>&1
Run the script as mysql:
# ./restore_innodb.bsh
Examine the resulting log file:
# more restore_innodb.log
InnoDB Backup Utility v1.4.0; Copyright 2003-2007 Innobase Oy
This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.
IMPORTANT: Please check that the copy-back run completes successfully.
At the end of a successful copy-back run innobackup
prints "innobackup completed OK!".
innobackup: Starting to copy MyISAM tables, indexes,
innobackup: .MRG, .TRG, .TRN, .opt, and .frm files
innobackup: in '/tmp/innodbsmpl_backup'
innobackup: back to original data directory '/opt/mysql/datafiles'
innobackup: Copying directory '/tmp/innodbsmpl_backup/mysql'
innobackup: Copying directory '/tmp/innodbsmpl_backup/innodbsmpl'
innobackup: Copying directory '/tmp/innodbsmpl_backup/test'
innobackup: Copying file '/tmp/innodbsmpl_backup/ibbackup_binlog_info'
innobackup: Starting to copy InnoDB tables and indexes
innobackup: in '/tmp/innodbsmpl_backup'
innobackup: back to original InnoDB data directory '/opt/mysql/datafiles'
innobackup: Copying file '/tmp/innodbsmpl_backup/ibdata1'
innobackup: Starting to copy InnoDB log files
innobackup: in '/tmp/innodbsmpl_backup'
innobackup: back to original InnoDB log directory '/opt/mysql/datafiles'
innobackup: Copying file '/tmp/innodbsmpl_backup/ib_logfile0'
innobackup: Copying file '/tmp/innodbsmpl_backup/ib_logfile1'
innobackup: Finished copying back files.
080317 12:40:14 innobackup: innobackup completed OK!
The backup has now been restored to the datadir.
Before starting the server, prepare the changes from the binlogs:
Obtain the starting change position from the following file:
# more /opt/mysql/datafiles/ibbackup_binlog_info
rac2_bin.000002 536
We will roll-forward starting from here. Stage binlogs changes as appropriate; for example:
Starting from the previously identified binlog and position:
# mysqlbinlog -f --start-position=536
/opt/mysql/datafiles/rac2_bin.000002 /opt/mysql/datafiles/rac2_bin.000003 > updates2apply
At this point, all the changes to roll the databases forward are contained in the
updates2apply file we have created.
Start the MySQL service:
# su - root
# service mysql-5.1.23 start
Starting MySQL [ OK ]
Query the sample table; notice how only the changes at the time of the backup are present:
mysql> select * from innodbsmpl;
+---------------+
| sampleval |
+---------------+
| Before Backup |
+---------------+
1 row in set (0.00 sec)
Roll forward the databases via:
# mysql --batch -u root -P 3307 --password --protocol=tcp -h localhost --force mysql < updates2apply
Enter password:
ERROR 1064 (42000) at line 17: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
You will notice there was an error, this is due to a bug that creates an invalid SQL statement at line 17. Because of bugs in the mysqlbinlog program, be sure to use the --force option whenever rolling forward your databases.
Query the sample table; notice how changes applied after the backup are now present:
mysql> select * from innodbsmpl;
+---------------+
| sampleval |
+---------------+
| Before Backup |
| After Backup |
+---------------+
2 rows in set (0.00 sec)
The innobackup tool gives you plenty of other options, such as starting MySQL directly from backups' directories (instead of restoring them to their original location).
Labels: MySQL
Compiling MySQL for InnoDB Storage Engine Use
Posted at Friday, February 29, 2008
MySQL distributions that intend on using the InnoDB Storage Engine need to be compiled in order to do so. I wanted to use this storage engine, but my binaries were not compiled with the correct option.
If your binaries aren't compiled with this option and you try to simply alter the my.cnf option file as appropriate, you will receive the following error upon startup:
The solution is to recompile the binaries with the --with-innodb option:
build_mysql.bsh from the source's home directory
After the configure, make, and make install operations are complete, update your option file to reflect (at a minimum) the following variables (customized for your environment, of course):
Restart your MySQL Server:
Run a couple of queries to verify that MySQL's InnoDB Storage Engine is now available:
It should now include the following row:
Also, check the following:
If your binaries aren't compiled with this option and you try to simply alter the my.cnf option file as appropriate, you will receive the following error upon startup:
/opt/mysql/5.1.23/libexec/mysqld: unknown variable 'innodb_data_home_dir=/opt/mysql/datafiles'
The solution is to recompile the binaries with the --with-innodb option:
build_mysql.bsh from the source's home directory
#!/bin/bash
MYSQL_BASEDIR=/opt/mysql/5.1.23
MYSQL_TCP_PORT=3307
MYSQL_SOCKET_FILE=$MYSQL_BASEDIR/socket/mysql-5123.sock
CFLAGS="-O3 -march=pentiumpro" CXX=gcc CXXFLAGS="-O3 -march=pentiumpro \
-felide-constructors -fno-exceptions -fno-rtti" ./configure \
--prefix=$MYSQL_BASEDIR --enable-assembler \
--with-mysqld-ldflags=-all-static --with-tcp-port=$MYSQL_TCP_PORT \
--with-unix-socket-path=$MYSQL_SOCKET_FILE --with-innodb
make
make install
./build_mysql.bsh
After the configure, make, and make install operations are complete, update your option file to reflect (at a minimum) the following variables (customized for your environment, of course):
# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir = /opt/mysql/datafiles
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /opt/mysql/datafiles
innodb_buffer_pool_size = 16M
innodb_additional_mem_pool_size = 2M
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
Restart your MySQL Server:
service mysql-5.1.23 start
Run a couple of queries to verify that MySQL's InnoDB Storage Engine is now available:
mysql> show engines ;
It should now include the following row:
| InnoDB | YES | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
Also, check the following:
mysql> show variables like 'have_innodb';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_innodb | YES |
+---------------+-------+
1 row in set (0.00 sec)
Labels: MySQL
Connecting to MySQL via MySQL Connector/J
Posted at Saturday, February 23, 2008
MySQL comes with a variety of connectors that allows you to access MySQL databases from the programming environment of your choice.
The following is a sample Java class that utilizes the JDBC driver that comes with MySQL Connector/J. Of course this is just a sample class, which you can edit and compile to verify the efficacy of your installation. This does not represent production-ready code.
Download the Connector/J software
MySQL Connector/J Software Downloads 5.1
Gunzip and untar the distribution, extracting the following jar to the location of your choice:
As you can see, after the gunzip and untar, I have opted to place the aforementioned jar file in /opt/mysql
Edit the following class, altering the connection information and the query it executes as appropriate:
MySQLJTest.java
After editing, you can use the following script to compile and run the program. Since, I am a heavy Oracle user, I have chosen to use the Java JDK that is in the databases' software home - you will want to modify the following script to account for the location of your JDK:
MySQLJTest.bsh
Once you have edited the scripts as appropriate, you can run the aforementioned script to compile and run the program:
If successful, you should have output similar to mine.
Here are the source files from this posting: MySQLJTest.tar
The following is a sample Java class that utilizes the JDBC driver that comes with MySQL Connector/J. Of course this is just a sample class, which you can edit and compile to verify the efficacy of your installation. This does not represent production-ready code.
Download the Connector/J software
MySQL Connector/J Software Downloads 5.1
Gunzip and untar the distribution, extracting the following jar to the location of your choice:
# ls -1 /opt/mysql/*jar
/opt/mysql/mysql-connector-java-5.1.5-bin.jar
As you can see, after the gunzip and untar, I have opted to place the aforementioned jar file in /opt/mysql
Edit the following class, altering the connection information and the query it executes as appropriate:
MySQLJTest.java
import java.io.*;
import java.sql.*;
public class MySQLJTest {
public static void main (String args[]){
Statement stmt = null;
ResultSet rs = null ;
Connection conn = null;
System.out.println("Beginning Test of MySQL Connector J");
System.out.println("Loading Driver...");
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
} catch (Exception e) {
System.out.println("Exception Loading MySQL Connector J Driver");
e.printStackTrace();
}
System.out.println("Obtaining Connection...");
try {
conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3307/mysql?" + "user=oracle&password=password");
} catch (SQLException se) {
System.out.println("SQLException: " + se.getMessage());
System.out.println("VendorError: " + se.getErrorCode());
}
System.out.println("Testing Connection with a query...");
try { stmt = conn.createStatement();
rs = stmt.executeQuery("SELECT User, Host, Password from user");
System.out.println("Processing the ResultSet...");
while (rs.next()) {
System.out.println(rs.getString(1) + "\t" + rs.getString(2) + "\t" + rs.getString(3) + "\n");
}
} catch (SQLException se) {
System.out.println("SQLException: " + se.getMessage());
System.out.println("VendorError: " + se.getErrorCode());
} finally {
if (rs != null) {
try { rs.close();
} catch (SQLException se) { }
rs = null;
}
if (stmt != null) {
try { stmt.close();
} catch (SQLException se) { }
stmt = null;
}
} //Ends Finally
} //Ends Main Method
} //Ends Class Definition
After editing, you can use the following script to compile and run the program. Since, I am a heavy Oracle user, I have chosen to use the Java JDK that is in the databases' software home - you will want to modify the following script to account for the location of your JDK:
MySQLJTest.bsh
#!/bin/bash
JAVA_HOME=/u03/app/oracle/product/db/11.1.0.6/jdk
CLASSPATH=/opt/mysql/mysql-connector-java-5.1.5-bin.jar:.:$JAVA_HOME/jre/lib:$CLASSPATH
$JAVA_HOME/bin/javac -classpath $CLASSPATH MySQLJTest.java
$JAVA_HOME/jre/bin/java -version
$JAVA_HOME/jre/bin/java -classpath $CLASSPATH MySQLJTest
Once you have edited the scripts as appropriate, you can run the aforementioned script to compile and run the program:
# ./MySQLJTest.bsh
java version "1.5.0_11"
Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_11-b03)
Java HotSpot(TM) Client VM (build 1.5.0_11-b03, mixed mode)
Beginning Test of MySQL Connector J
Loading Driver...
Obtaining Connection...
Testing Connection with a query...
Processing the ResultSet...
root localhost 5d2e19393cc5ef67
root rac2.colestock.test 5d2e19393cc5ef67
root 127.0.0.1 5d2e19393cc5ef67
oracle rac2.colestock.com 5d2e19393cc5ef67
oracle localhost 5d2e19393cc5ef67
oracle 192.168.1.104 5d2e19393cc5ef67
oracle 127.0.0.1 5d2e19393cc5ef67
If successful, you should have output similar to mine.
Here are the source files from this posting: MySQLJTest.tar
Installing MySQL GUI Tools Bundle (Bug 28143, 26120)
Posted at Friday, February 22, 2008
I had plenty of problems installing MySQL's GUI Tools Bundle 5.0-r12:
MySQL 5.0 GUI Tools Bundle Downloads
Initially I tried building and compiling from source, but there were so many dependencies, etc. that I opted to install via RPM:
This, however, fails declaring a Java dependency. It appears as though I am running into the following bugs:
Bug 28143
Bug 26120
To my knowledge, neither of these bugs have been resolved for the version in question. Provided that I don't intend on using the MySQL Workbench, I can install the RPMs by forcing (e.g. --nodeps):
The MySQL Query Browser is what I am interested in using, so I test via:
This launches the mysql-query-browser and I test my connection as follows:
MySQL 5.0 GUI Tools Bundle Downloads
Initially I tried building and compiling from source, but there were so many dependencies, etc. that I opted to install via RPM:
# rpm -ivh mysql-gui-tools-5.0r12-1rhel4.i386.rpm \
mysql-administrator-5.0r12-1rhel4.i386.rpm \
mysql-query-browser-5.0r12-1rhel4.i386.rpm \
mysql-migration-toolkit-5.0r12-1rhel4.i386.rpm
error: Failed dependencies:
libjvm.so is needed by mysql-migration-toolkit-5.0r12-1rhel4.i386
libjvm.so(SUNWprivate_1.1) is needed by mysql-migration-toolkit-5.0r12-1rhel4.i386
libmysqlclient_r.so.15 is needed by mysql-migration-toolkit-5.0r12-1rhel4.i386
libmysqlclient_r.so.15(libmysqlclient_15) is needed by mysql-migration-toolkit-5.0r12-1rhel4.i386
This, however, fails declaring a Java dependency. It appears as though I am running into the following bugs:
Bug 28143
Bug 26120
To my knowledge, neither of these bugs have been resolved for the version in question. Provided that I don't intend on using the MySQL Workbench, I can install the RPMs by forcing (e.g. --nodeps):
# rpm -ivh --nodeps mysql-gui-tools-5.0r12-1rhel4.i386.rpm \
mysql-administrator-5.0r12-1rhel4.i386.rpm \
mysql-query-browser-5.0r12-1rhel4.i386.rpm \
mysql-migration-toolkit-5.0r12-1rhel4.i386.rpm
The MySQL Query Browser is what I am interested in using, so I test via:
# export DISPLAY=192.168.1.104:0.0
# mysql-query-browser
This launches the mysql-query-browser and I test my connection as follows:
Labels: Installation, MySQL
Installing Multiple Versions of MySQL
Posted at Wednesday, February 20, 2008
As with any RDBMS, there are circumstances in which you need to run multiple, differing versions on the same server.
This posts reviews my preferred way of accomplishing this for MySQL on Linux. The server in question already has a previous 4.X.X version of MySQL. Rather than overwrite this, I would like to install 5.1.23, decoupling it from the existing MySQL installation. In order to do this, I will be compiling MySQL from a source distribution specifying a custom TCP port as well as custom file locations, etc. Following this scheme, you could install many different versions of MySQL on a single server.
Here are the steps I followed:
Download the desired source distribution:
MySQL 5.1 Source Downloads
As root, create any necessary directories, transfer to server, gunzip, and extract:
Make the necessary directories for the installation, initially all owned by root:
Configure and make from the source's base directory; I normally place the configure and make steps into a script:
Modify the values to suit your environment and then execute the script:
Install the MySQL system tables:
Change the ownership of key directories:
Create a custom .cnf file and modify the appropriate values:
Alter the following values:
Start MySQL manually via mysqld_safe to test:
Configure this particular server for automatic startup/shutdown:
The mysql.server script looks for your option file in your $basedir; to support this, move the previously created option file into the $basedir:
Now your service is configured to automatically start and stop with the appropriate system run levels; optionally you can start and stop manually via:
Confirm your installation's variables:
Post Configuration Steps
Drop the test database:
Specify password for the root account and drop the anonymous users:
Create an admin user besides root (I will use the oracle account on my machine):
Optionally, set-up the alternate admin user's Unix environment and test:
Alter appropriate profile with
Create .my.cnf in the $HOME directory
Test connection
At this point, your sandbox should be ready to go...
This posts reviews my preferred way of accomplishing this for MySQL on Linux. The server in question already has a previous 4.X.X version of MySQL. Rather than overwrite this, I would like to install 5.1.23, decoupling it from the existing MySQL installation. In order to do this, I will be compiling MySQL from a source distribution specifying a custom TCP port as well as custom file locations, etc. Following this scheme, you could install many different versions of MySQL on a single server.
Here are the steps I followed:
Download the desired source distribution:
MySQL 5.1 Source Downloads
As root, create any necessary directories, transfer to server, gunzip, and extract:
# mkdir -p /opt/mysql/build
# mv mysql-5.1.23-rc.tar.gz /opt/mysql/build/.
# cd /opt/mysql/build/.
# gunzip mysql-5.1.23-rc.tar.gz
# tar -xvf mysql-5.1.23-rc.tar
Make the necessary directories for the installation, initially all owned by root:
mkdir -p /opt/mysql/datafiles
mkdir -p /opt/mysql/5.1.23
mkdir -p /opt/mysql/5.1.23/socket
mkdir -p /opt/mysql/5.1.23/logs
mkdir -p /opt/mysql/5.1.23/pid
Configure and make from the source's base directory; I normally place the configure and make steps into a script:
# cd /opt/mysql/build/5.1.23
# more ./build_mysql.bsh
#!/bin/bash
MYSQL_BASEDIR=/opt/mysql/5.1.23
MYSQL_TCP_PORT=3307
MYSQL_SOCKET_FILE=$MYSQL_BASEDIR/socket/mysql-5123.sock
CFLAGS="-O3 -march=pentiumpro" CXX=gcc CXXFLAGS="-O3 -march=pentiumpro \
-felide-constructors -fno-exceptions -fno-rtti"
./configure \
--prefix=$MYSQL_BASEDIR --enable-assembler \
--with-mysqld-ldflags=-all-static --with-tcp-port=$MYSQL_TCP_PORT \
--with-unix-socket-path=$MYSQL_SOCKET_FILE
make
make install
Modify the values to suit your environment and then execute the script:
./build_mysql.bsh > build_mysql.log
Install the MySQL system tables:
# cd /opt/mysql/5.1.23/bin
# ./mysql_install_db --user=mysql --datadir=/opt/mysql/datafiles
Change the ownership of key directories:
# chown -R mysql:mysql /opt/mysql/5.1.23/logs
# chown -R mysql:mysql /opt/mysql/5.1.23/socket
# chown -R mysql:mysql /opt/mysql/5.1.23/pid
# chown -R mysql:mysql /opt/mysql/datafiles
Create a custom .cnf file and modify the appropriate values:
# cp /opt/mysql/build/mysql-5.1.23-rc/support-files/my-medium.cnf /etc/mysql-5.1.23.cnf
Alter the following values:
pid-file = /opt/mysql/5.1.23/pid/mysqld-5123.pid
datadir = /opt/mysql/datafiles
log = /opt/mysql/5.1.23/logs/rac2.5123.log
log-error = /opt/mysql/5.1.23/logs/rac2.5123.err
basedir = /opt/mysql/5.1.23
Start MySQL manually via mysqld_safe to test:
# cd /opt/mysql/5.1.23
# ./bin/mysqld_safe --defaults-file=/etc/mysql-5.1.23.cnf --user=mysql &
Configure this particular server for automatic startup/shutdown:
# cp /opt/mysql/build/mysql-5.1.23-rc/support-files/mysql.server /etc/init.d/mysql-5.1.23
# chown o+x /etc/init.d/mysql-5.1.23
# chkconfig --add mysql-5.1.23
# chkconfig --level 345 mysql-5.1.23 on
The mysql.server script looks for your option file in your $basedir; to support this, move the previously created option file into the $basedir:
# mv /etc/mysql-5.1.23.cnf /opt/mysql/5.1.23/my.cnf
Now your service is configured to automatically start and stop with the appropriate system run levels; optionally you can start and stop manually via:
# service mysql-5.1.23 start
# service mysql-5.1.23 stop
Confirm your installation's variables:
# /opt/mysql/5.1.23/bin/mysqladmin variables
+---------------------------------+--------------------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------------------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| autocommit | ON |
| automatic_sp_privileges | ON |
| back_log | 50 |
| basedir | /opt/mysql/5.1.23/ |
| big_tables | OFF |
| binlog_cache_size | 32768 |
| binlog_format | MIXED |
| bulk_insert_buffer_size | 8388608 |
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /opt/mysql/5.1.23/share/mysql/charsets/ |
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
| completion_type | 0 |
| concurrent_insert | 1 |
| connect_timeout | 10 |
| datadir | /opt/mysql/datafiles/ |
| date_format | %Y-%m-%d |
| datetime_format | %Y-%m-%d %H:%i:%s |
| default_week_format | 0 |
| delay_key_write | ON |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
| div_precision_increment | 4 |
| engine_condition_pushdown | ON |
| error_count | 0 |
| event_scheduler | OFF |
| expire_logs_days | 0 |
| flush | OFF |
| flush_time | 0 |
| foreign_key_checks | ON |
| ft_boolean_syntax | + -><()~*:""&| |
| ft_max_word_len | 84 |
| ft_min_word_len | 4 |
| ft_query_expansion_limit | 20 |
| ft_stopword_file | (built-in) |
| general_log | ON |
| general_log_file | /opt/mysql/5.1.23/logs/rac2.5123.log |
| group_concat_max_len | 1024 |
| have_compress | YES |
| have_crypt | YES |
| have_csv | YES |
| have_dynamic_loading | NO |
| have_geometry | YES |
| have_innodb | NO |
| have_ndbcluster | NO |
| have_openssl | NO |
| have_partitioning | NO |
| have_query_cache | YES |
| have_rtree_keys | YES |
| have_ssl | NO |
| have_symlink | YES |
| hostname | rac2.colestock.test |
| identity | 0 |
| init_connect | |
| init_file | |
| init_slave | |
| insert_id | 0 |
| interactive_timeout | 28800 |
| join_buffer_size | 131072 |
| keep_files_on_create | OFF |
| key_buffer_size | 16777216 |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| language | /opt/mysql/5.1.23/share/mysql/english/ |
| large_files_support | ON |
| large_page_size | 0 |
| large_pages | OFF |
| last_insert_id | 0 |
| lc_time_names | en_US |
| license | GPL |
| local_infile | ON |
| locked_in_memory | OFF |
| log | ON |
| log_bin | ON |
| log_bin_trust_function_creators | OFF |
| log_bin_trust_routine_creators | OFF |
| log_error | /opt/mysql/5.1.23/logs/rac2.5123.err |
| log_output | FILE |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | OFF |
| log_slow_queries | OFF |
| log_warnings | 1 |
| long_query_time | 10.000000 |
| low_priority_updates | OFF |
| lower_case_file_system | OFF |
| lower_case_table_names | 0 |
| max_allowed_packet | 1048576 |
| max_binlog_cache_size | 4294967295 |
| max_binlog_size | 1073741824 |
| max_connect_errors | 10 |
| max_connections | 151 |
| max_delayed_threads | 20 |
| max_error_count | 64 |
| max_heap_table_size | 16777216 |
| max_insert_delayed_threads | 20 |
| max_join_size | 4294967295 |
| max_length_for_sort_data | 1024 |
| max_prepared_stmt_count | 16382 |
| max_relay_log_size | 0 |
| max_seeks_for_key | 4294967295 |
| max_sort_length | 1024 |
| max_sp_recursion_depth | 0 |
| max_tmp_tables | 32 |
| max_user_connections | 0 |
| max_write_lock_count | 4294967295 |
| min_examined_row_limit | 0 |
| multi_range_count | 256 |
| myisam_data_pointer_size | 6 |
| myisam_max_sort_file_size | 2147483647 |
| myisam_recover_options | OFF |
| myisam_repair_threads | 1 |
| myisam_sort_buffer_size | 8388608 |
| myisam_stats_method | nulls_unequal |
| myisam_use_mmap | OFF |
| net_buffer_length | 8192 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| new | OFF |
| old | OFF |
| old_alter_table | OFF |
| old_passwords | ON |
| open_files_limit | 1024 |
| optimizer_prune_level | 1 |
| optimizer_search_depth | 62 |
| pid_file | /opt/mysql/5.1.23/pid/mysqld-5123.pid |
| plugin_dir | /opt/mysql/5.1.23/lib/mysql |
| port | 3307 |
| preload_buffer_size | 32768 |
| protocol_version | 10 |
| pseudo_thread_id | 0 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 0 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
| rand_seed1 | |
| rand_seed2 | |
| range_alloc_block_size | 4096 |
| read_buffer_size | 262144 |
| read_only | OFF |
| read_rnd_buffer_size | 524288 |
| relay_log | |
| relay_log_index | |
| relay_log_info_file | relay-log.info |
| relay_log_purge | ON |
| relay_log_space_limit | 0 |
| rpl_recovery_rank | 0 |
| secure_auth | OFF |
| secure_file_priv | |
| server_id | 1 |
| skip_external_locking | ON |
| skip_networking | OFF |
| skip_show_database | OFF |
| slave_compressed_protocol | OFF |
| slave_load_tmpdir | /tmp |
| slave_net_timeout | 3600 |
| slave_skip_errors | OFF |
| slave_transaction_retries | 10 |
| slow_launch_time | 2 |
| slow_query_log | OFF |
| slow_query_log_file | /opt/mysql/5.1.23/pid/mysqld-5123-slow.log |
| socket | /opt/mysql/5.1.23/socket/mysql-5123.sock |
| sort_buffer_size | 524288 |
| sql_auto_is_null | ON |
| sql_big_selects | ON |
| sql_big_tables | OFF |
| sql_buffer_result | OFF |
| sql_log_bin | ON |
| sql_log_off | OFF |
| sql_log_update | ON |
| sql_low_priority_updates | OFF |
| sql_max_join_size | 4294967295 |
| sql_mode | |
| sql_notes | ON |
| sql_quote_show_create | ON |
| sql_safe_updates | OFF |
| sql_select_limit | 4294967295 |
| sql_slave_skip_counter | |
| sql_warnings | OFF |
| ssl_ca | |
| ssl_capath | |
| ssl_cert | |
| ssl_cipher | |
| ssl_key | |
| storage_engine | MyISAM |
| sync_binlog | 0 |
| sync_frm | ON |
| system_time_zone | MST |
| table_definition_cache | 128 |
| table_lock_wait_timeout | 50 |
| table_open_cache | 64 |
| table_type | MyISAM |
| thread_cache_size | 0 |
| thread_handling | one-thread-per-connection |
| thread_stack | 196608 |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| timestamp | 1203539498 |
| tmp_table_size | 16777216 |
| tmpdir | /tmp |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| tx_isolation | REPEATABLE-READ |
| unique_checks | ON |
| updatable_views_with_limit | YES |
| version | 5.1.23-rc-log |
| version_comment | Source distribution |
| version_compile_machine | i686 |
| version_compile_os | redhat-linux-gnu |
| wait_timeout | 28800 |
| warning_count | 0 |
+---------------------------------+--------------------------------------------+
Post Configuration Steps
Drop the test database:
# ./mysqladmin -u root drop test
Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.
Do you really want to drop the 'test' database [y/N] y
Database "test" dropped
Specify password for the root account and drop the anonymous users:
# ./mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.23-rc-log Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('password');
Query OK, 0 rows affected (0.00 sec)
mysql> SET PASSWORD FOR 'root'@'rac2.colestock.test' = PASSWORD('password');
Query OK, 0 rows affected (0.00 sec)
mysql> SET PASSWORD FOR 'root'@'127.0.0.1' = PASSWORD('password');
Query OK, 0 rows affected (0.00 sec)
mysql> DELETE FROM mysql.user WHERE User='';
Query OK, 2 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT HOST, User, Password from mysql.user;
+---------------------+------+------------------+
| HOST | User | Password |
+---------------------+------+------------------+
| localhost | root | 5d2e19393cc5ef67 |
| rac2.colestock.test | root | 5d2e19393cc5ef67 |
| 127.0.0.1 | root | 5d2e19393cc5ef67 |
+---------------------+------+------------------+
3 rows in set (0.00 sec)
Create an admin user besides root (I will use the oracle account on my machine):
mysql> CREATE USER 'oracle'@'localhost' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT User, Host, Password from mysql.user;
+--------+---------------------+------------------+
| User | Host | Password |
+--------+---------------------+------------------+
| root | localhost | 5d2e19393cc5ef67 |
| root | rac2.colestock.test | 5d2e19393cc5ef67 |
| root | 127.0.0.1 | 5d2e19393cc5ef67 |
| oracle | localhost | 5d2e19393cc5ef67 |
+--------+---------------------+------------------+
4 rows in set (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO 'oracle'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT RELOAD, PROCESS ON *.* TO 'oracle'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
Optionally, set-up the alternate admin user's Unix environment and test:
Alter appropriate profile with
PATH=/opt/mysql/5.1.23/bin:$PATH:$HOME/bin
Create .my.cnf in the $HOME directory
# more ~/.my.cnf
[client]
host = localhost
database = mysql
user = oracle
password = password
protocol = tcp
port = 3307
socket = /opt/mysql/5.1.23/socket/mysql-5123.sock
Test connection
# which mysql
/opt/mysql/5.1.23/bin/mysql
# mysql
mysql> status
--------------
mysql Ver 14.14 Distrib 5.1.23-rc, for redhat-linux-gnu (i686) using EditLine wrapper
Connection id: 51
Current database: mysql
Current user: oracle@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.1.23-rc-log Source distribution
Protocol version: 10
Connection: localhost via TCP/IP
Server characterset: latin1
Db characterset: latin1
Client characterset: latin1
Conn. characterset: latin1
TCP port: 3307
Uptime: 1 hour 10 min 27 sec
Threads: 1 Questions: 104 Slow queries: 0 Opens: 29 Flush tables: 1 Open tables: 23 Queries per second avg: 0.24
--------------
At this point, your sandbox should be ready to go...
Labels: Installation, MySQL
How To Install MySQL 5.0.X on Red Hat ES 4.0
Posted at Thursday, August 31, 2006
Keeping with the non-Oracle-centric postings, here are my notes on how to install MySQL on a fresh Red Hat ES 4.0 install:
How to Install MySQL 5.0.X on Red Hat ES 4.0
How to Install MySQL 5.0.X on Red Hat ES 4.0
Labels: Installation, MySQL
_507.png)
Subscribe
