Archive | Databases

1. Create the tmp directory:

2. Set permissions:

3. Determine mysql user id:

4. Edit /etc/fstab and add the following line:

5. Mount the new tmpfs partition:

6. Edit /etc/my.cnf and set location of tmp directory:

7. Restart mysqd

To setup an alternate instance of MySQL listening on TCP port 3307 on a RHEL5 server follow these steps.

1. Setup a new MySQL config file.

cp /etc/my.cnf /etc/my-3307.cnf

1	cp /etc/my.cnf /etc/my-3307.cnf

Add a line like this:

port = 3307

1	port = 3307

to the “[mysqld]” section.

Edit /etc/my-3307.cnf and change:

datadir=/var/lib/mysql

1	datadir=/var/lib/mysql

to

datadir=/var/lib/mysql-3307

1	datadir=/var/lib/mysql-3307

Change:

socket=/var/lib/mysql/mysql.sock

1	socket=/var/lib/mysql/mysql.sock

to:

socket=/var/lib/mysql-3307/mysql.sock

1	socket=/var/lib/mysql-3307/mysql.sock

Change the following lines:

log-slow-queries=/var/lib/mysqllogs/slow-log log-bin=/var/lib/mysqllogs/bin-log log-bin-index=/var/lib/mysqllogs/bin-log.index relay-log=/var/lib/mysqllogs/relay-log relay-log-index=/var/lib/mysqllogs/relay-log.index log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid

1 2 3 4 5 6 7

log-slow-queries=/var/lib/mysqllogs/slow-log log-bin=/var/lib/mysqllogs/bin-log log-bin-index=/var/lib/mysqllogs/bin-log.index relay-log=/var/lib/mysqllogs/relay-log relay-log-index=/var/lib/mysqllogs/relay-log.index log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid

to:

log-slow-queries=/var/lib/mysqllogs-3307/slow-log log-bin=/var/lib/mysqllogs-3307/bin-log log-bin-index=/var/lib/mysqllogs-3307/bin-log.index relay-log=/var/lib/mysqllogs-3307/relay-log relay-log-index=/var/lib/mysqllogs-3307/relay-log.index log-error=/var/log/mysqld-3307.log pid-file=/var/run/mysqld/mysqld-3307.pid

1 2 3 4 5 6 7

log-slow-queries=/var/lib/mysqllogs-3307/slow-log log-bin=/var/lib/mysqllogs-3307/bin-log log-bin-index=/var/lib/mysqllogs-3307/bin-log.index relay-log=/var/lib/mysqllogs-3307/relay-log relay-log-index=/var/lib/mysqllogs-3307/relay-log.index log-error=/var/log/mysqld-3307.log pid-file=/var/run/mysqld/mysqld-3307.pid

Change server-id to a unique value:

server-id=2

1	server-id=2

2. Setup a new service control script.

cp /etc/init.d/mysqld /etc/init.d/mysqld-3307

1	cp /etc/init.d/mysqld /etc/init.d/mysqld-3307

Edit /etc/init.d/mysqld-3307 and add this line:

MYCNF=/etc/my-3307.cnf

1	MYCNF=/etc/my-3307.cnf

directly after:

# Source networking configuration. . /etc/sysconfig/network

1 2	# Source networking configuration. . /etc/sysconfig/network

so that you have:

# Source networking configuration. . /etc/sysconfig/network MYCNF=/etc/my-3307.cnf

1 2 3 4

# Source networking configuration. . /etc/sysconfig/network   MYCNF=/etc/my-3307.cnf

Next change this function:

get_mysql_option(){ result=`/usr/bin/my_print_defaults "$1" | sed -n "s/^--$2=//p" | tail -n 1` if [ -z "$result" ]; then # not found, use default result="$3" fi }

1 2 3 4 5 6 7

get_mysql_option(){         result=`/usr/bin/my_print_defaults "$1" | sed -n "s/^--$2=//p" | tail -n 1`         if [ -z "$result" ]; then             # not found, use default             result="$3"         fi }

to:

get_mysql_option(){ result=`/usr/bin/my_print_defaults -c $MYCNF "$1" | sed -n "s/^--$2=//p" | tail -n 1` if [ -z "$result" ]; then # not found, use default result="$3" fi }

1 2 3 4 5 6 7

get_mysql_option(){         result=`/usr/bin/my_print_defaults -c $MYCNF "$1" | sed -n "s/^--$2=//p" | tail -n 1`         if [ -z "$result" ]; then             # not found, use default             result="$3"         fi }

Notice that the change is to add “-c $MYCNF” to the call to “my_print_defaults”.

Finally run the following search/replace commands to fixup the program name, add defaults file to mysqld_safe call and set unique pid and subsys files:

replace 'prog="MySQL"' 'prog="MySQL-3307"' -- /etc/init.d/mysqld-3307 replace '/usr/bin/mysqld_safe' '/usr/bin/mysqld_safe --defaults-file=$MYCNF' \ -- /etc/init.d/mysqld-3307 replace 'mysqld.pid' 'mysqld-3307.pid' -- /etc/init.d/mysqld-3307 replace '/var/lock/subsys/mysqld' '/var/lock/subsys/mysqld-3307' -- /etc/init.d/mysqld-3307

1 2 3 4 5

replace 'prog="MySQL"' 'prog="MySQL-3307"' -- /etc/init.d/mysqld-3307 replace '/usr/bin/mysqld_safe' '/usr/bin/mysqld_safe --defaults-file=$MYCNF' \   -- /etc/init.d/mysqld-3307 replace 'mysqld.pid' 'mysqld-3307.pid' -- /etc/init.d/mysqld-3307 replace '/var/lock/subsys/mysqld' '/var/lock/subsys/mysqld-3307' -- /etc/init.d/mysqld-3307

3. Setup directories

mkdir /var/lib/mysql-3307 /var/lib/mysqllogs-3307 chown mysql.mysql /var/lib/mysql-3307/ /var/lib/mysqllogs-3307 chmod o-rwx /var/lib/mysqllogs-3307

1 2 3

mkdir /var/lib/mysql-3307 /var/lib/mysqllogs-3307 chown mysql.mysql /var/lib/mysql-3307/ /var/lib/mysqllogs-3307 chmod o-rwx /var/lib/mysqllogs-3307

4. Set service to start on boot

/sbin/chkconfig mysqld-3307 on

1	/sbin/chkconfig mysqld-3307 on

5. Start the new instance:

/sbin/service mysqld-3307 start

1	/sbin/service mysqld-3307 start

On the first startup you should see some output like this:

Initializing MySQL database: Installing MySQL system tables... OK Filling help tables... OK To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands: /usr/bin/mysqladmin -u root password 'new-password' /usr/bin/mysqladmin -u root -h 244418-web3.www.idtweet.com password 'new-password' Alternatively you can run: /usr/bin/mysql_secure_installation which will also give you the option of removing the test databases and anonymous user created by default. This is strongly recommended for production servers. See the manual for more instructions. You can start the MySQL daemon with: cd /usr ; /usr/bin/mysqld_safe & You can test the MySQL daemon with mysql-test-run.pl cd mysql-test ; perl mysql-test-run.pl Please report any problems with the /usr/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 [ OK ] Starting MySQL-3307: [ OK ]

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36

Initializing MySQL database:  Installing MySQL system tables... OK Filling help tables... OK   To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system   PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands: /usr/bin/mysqladmin -u root password 'new-password' /usr/bin/mysqladmin -u root -h 244418-web3.www.idtweet.com password 'new-password'   Alternatively you can run: /usr/bin/mysql_secure_installation   which will also give you the option of removing the test databases and anonymous user created by default.  This is strongly recommended for production servers.   See the manual for more instructions.   You can start the MySQL daemon with: cd /usr ; /usr/bin/mysqld_safe &   You can test the MySQL daemon with mysql-test-run.pl cd mysql-test ; perl mysql-test-run.pl   Please report any problems with the /usr/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                                                            [  OK  ]   Starting MySQL-3307:                                       [  OK  ]

6. Set MySQL root password.

/usr/bin/mysqladmin -P 3307 -h 127.0.0.1 -u root --password="" password password 'new-password'

1	/usr/bin/mysqladmin -P 3307 -h 127.0.0.1 -u root --password="" password password 'new-password'

And that’s it. You now have an new instance of MySQL listening on port 3307.

Remember that you must tell the mysql command line utilities where to find the instance. For example:

mysql -P 3307 -h 127.0.0.1

1	mysql -P 3307 -h 127.0.0.1

or

mysql -S /var/lib/mysql-3307/mysql.sock

1	mysql -S /var/lib/mysql-3307/mysql.sock

Also keep in mind that by default any .my.cnf file in your home directory will be used. You may have to override settings in the .my.cnf file and explicitly provide the user and password when connecting.

Came across a handy configuration setting for mysql:

Controls how many days of binary log files should be retained. Very good way to keep the binary logs from over flowing available storage.

Trying to dump and import a mysql database from an old MySQL 3.x server to a 4.x server. Here’s an exerpt from the dump file:

What’s happening, is when it inserts the first row, it’s
auto-incrementing the key value to 1, so the next row that tries to
import gets a “Duplicate entry ‘1’ for key 1” error.

The solution was to add the following line to the top of your sql dump file:

Later versions of mysql do this automatically.

Problem is the first insert tries to put a 0 into the autoincrement
field. Normal behavior is for the 0 to be converted to the next
available increment which is one. Then the second row fails because
there is already a 1 in the table. The mode setting above tells mysql to
allow the 0 in the auto increment field.

The RHEL AS4 server had an active RHN membership so installing MySQL server was a quick and easy. Here are the commands:

# up2date –install mysql-server

# /sbin/service mysqld start

# mysqladmin -u root password “my_root_password”

# mysqladmin -u root -h localhost -p password “my_root_password”

The last command will prompt for a password. Enter the value for “my_root_password”.

And that’s all there was to it!