Came across a handy configuration setting for mysql:
|
1 |
<em>expire_logs_days = 7</em> |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
DROP TABLE IF EXISTS ds_categories; CREATE TABLE ds_categories ( catID int(11) NOT NULL auto_increment, catName varchar(80) default NULL, catDesc text NOT NULL, catPhoto varchar(200) NOT NULL default '', catPhotoWidth int(11) NOT NULL default '0', catPhotoHeight int(11) NOT NULL default '0', PRIMARY KEY (catID), UNIQUE KEY catID (catID) ) TYPE=MyISAM; INSERT INTO ds_categories VALUES (0,'DEFAULT','','',0,0); INSERT INTO ds_categories VALUES (1,'Products','','',0,0); |
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:
|
1 |
SET SQL_MODE='NO_AUTO_VALUE_ON_ZERO'; |
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!
Recent I had to install MySQL on a RHEL 3 server. Normally I’d do this with RPMs but in this case the client needed an install that exactly matched another server which had been installed using the TAR/GZ binary distribution. Here are the steps used for the install: 1. Download the binary TAR/GZ distribution. In this case it was named mysql-standard-4.1.13-pc-linux-gnu-i686.tar.gz and I downloaded it to /root/archive. 2. Unpack the distribution.
|
1 2 3 |
cd /root/archive tar -xvzf mysql-standard-4.1.13-pc-linux-gnu-i686.tar.gz |
3. The steps provided in the INSTALL-BINARY text file included in the distribution outline the following steps:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
shell> groupadd mysql shell> useradd -g mysql mysql shell> cd /usr/local shell> gunzip < /PATH/TO/MYSQL-VERSION-OS.tar.gz | tar xvf - shell> ln -s FULL-PATH-TO-MYSQL-VERSION-OS mysql shell> cd mysql shell> scripts/mysql_install_db --user=mysql shell> chown -R root . shell> chown -R mysql data shell> chgrp -R mysql . shell> bin/mysqld_safe --user=mysql & |
Taking these one at a time:
|
1 |
shell> groupadd mysql |
Skip this step. The mysql group already exists so there is no need to add it.
|
1 |
shell> useradd -g mysql mysql |
Skip this step. The mysql user already exists so there is no need to add it.
|
1 2 3 4 5 |
shell> cd /usr/local shell> gunzip < /root/archive/mysql-standard-4.1.13-pc-linux-gnu-i686.tar.gz | tar xvf - shell> ln -s mysql-standard-4.1.13-pc-linux-gnu-i686 mysql |
Complete this steps as provided.
|
1 2 3 4 5 6 7 |
shell> cd mysql shell> scripts/mysql_install_db --user=mysql shell> chown -R root . shell> chown -R mysql data shell> chgrp -R mysql . |
Complete these steps as provided.
|
1 |
shell> bin/mysqld_safe --user=mysql & |
We’ll skip this step for now since
Use this command to increase the max memory that a single process can use:
|
1 |
/sbin/sysctl -w kernel.task_size=3221225472 |
Add to /etc/sysctl.conf to reload on each boot.
Reliable Penguin provides managed web hosting, systems administration, website and server migrations, and expert consulting.
Submit the form—or for immediate service call 866-649-7984.