linux:mysql - Solin Expertise Center

Trace: • installation_and_configuration_issues
This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
=====MySQL=====






<code c>
[root@1038 bin]# ./mysql_install_db --user=mysql
Installing all prepared tables
041122 20:51:29  /usr/libexec/mysqld: Shutdown Complete


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 !
This is done with:
/usr/bin/mysqladmin -u root  password 'new-password'
/usr/bin/mysqladmin -u root -h 1038.flexservers.com  password 'new-password'
See the manual for more instructions.

NOTE:  If you are upgrading from a MySQL <= 3.22.10 you should run
the /usr/bin/mysql_fix_privilege_tables. Otherwise you will not be
able to use the new GRANT command!

You can start the MySQL daemon with:
cd /usr ; /usr/bin/safe_mysqld &

You can test the MySQL daemon with the benchmarks in the 'sql-bench' directory:
cd sql-bench ; run-all-tests

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 https://order.mysql.com
</code>
 



N.B.: there is a max length of about 17 or 18 characters for MySQL user names! My Unix name campinglachassagne for instance did not properly translate into a MySQL user name (Virtualmin truncates the username automatically).




=====Thursday, november the 25th, 2004=====


Server has come back online after hard disk crash. Flexservers people have fixed it and restored fresh install state.



Up till now, I have reinstalled the iptables-rules.



Situation: we have an Apache2 server, which should be replaced by an Apache1 server to safely work with php in a production environment.










=====MySQL - Part 2=====


It turns out that the standard installation of Plesk on the Flexserver has made a mysql user //admin//, with the same password as the Plesk user //admin//. It also turns out that the Plesk password is stored here:



<code c>
/etc/psa/.psa.shadow 
</code>


In a plain text file...





I have added an "ordinary" user //mysql //for use on the webserver:



<code c>
mysql> GRANT ALL PRIVILEGES ON *.* TO 'mysql'@'localhost'
    -> IDENTIFIED BY '************';
</code>


This user cannot grant privileges to itself or any other users.




====Dropping databases from MySQL====
After experimenting with several control panels (for webserver administration), I felt the acute need to delete some databases. This is the syntax: ''**DROP DATABASE database_name;**''



But beware! Most of the time, some files remain in MySQL's directory/file structure. Look up ''**/var/lib/mysql/**'' and see if there are any files left in the directory "database_name". Delete any file you may encounter in this directory, then issue the command once more:



<code c>
mysql> DROP DATABASE example;
Query OK, 0 rows affected (0.00 sec)
</code>





====20051130 Starting and stopping MySQL====
The MySQL server is actually a daemon called mysqld. The binary file called mysqld can be found here: ''**/usr/libexec**''.



There is also a shell script called mysqld. So far, I have used this script to start and stop the mysqld daemon. You use these options like this:



<code c>
/etc/rc.d/init.d/mysqld stop
/etc/rc.d/init.d/mysqld start
</code>


If you use the //start //option, the script seems to execute the following command:



<code c>
/usr/bin/safe_mysqld  --defaults-file=/etc/my.cnf >/dev/null 2>&1 &
</code>


From the manual:



safe_mysqld  adds  some  safety  features such as restarting the server when an error occurs and logging run-time information to a log file.

However, the safe_mysqld command has (surprise, surprise) less options than mysqld itself. For instance, safe_mysqld lacks the ''**--log-update **''and ''**--log-bin**'' options for mysqld. Fortunately, the documentation states:



//Note that all options on the command line to safe_mysqld are passed to mysqld. If you wants to use any options in safe_mysqld that mysqld doesn't support, you must specify these in the option file. //



Now, something has to be done about the mysqld script, because we really need the -''**log-bin **''option (or, if that undocumented option does not work in version 3.23.58, ''**--log-update**'').




===False failure message during startup===


But first, we have to get rid of an annoying thing that has been bothering me ever since I installed MySQL. Whenever you start mysql (''**mysqld start**''), the script will tell you that the startup of MySQL failed, even when it did not. Deleting anonymous users is the cause of this trouble, as is explained in a text derived from [[http://bradthemad.org/tech/notes/mysql_setup.php|http://bradthemad.org/tech/notes/mysql_setup.php]]:



//The startup script /etc/rc.d/init.d/mysqld actually uses the anonymous account when starting the MySQL server to do a "ping" and verify that it has actually started. Removing the anonymous account will cause this check to fail, even though the DB may be up and running fine. To remedy this, edit the script, and make the following edit://



<code c>
# If you've removed anonymous users, this line must be changed to
# use a user that is allowed to ping mysqld.
#ping="/usr/bin/mysqladmin -uUNKNOWN_MYSQL_USER ping"
ping="pidof mysqld"
</code>






====Logging updates to the database====
If you ever feel the need to backup your MySQL database, use mysqldump. It really works wonders, but also produces a lot of data. So, to implement incremental backups you need to log all updates that have occurred since that last full backup. This is where the ''**--log-bin**'' option comes into play. If you start the mysqld daemon with this option, then the database server logs every tiny update into a binary file.

The mysqld shell script, located in ''**/etc/rc.d/init.d/mysqld**'', uses the following configuration file: ''**/etc/my.cnf**''.

In this configuration file, find the ''**[mysqld]**'' section, and add this under it:

<code c>
log-bin
</code>


The actual log files are stored in your MySQL data directory:



<code c>
[root@1038 mysql]# pwd
/var/lib/mysql
[root@1038 mysql]# ls -l *-bin.*
-rw-rw----  1 mysql mysql   8675 Nov 30 19:43 1038-bin.001
-rw-rw----  1 mysql mysql  51191 Nov 30 19:56 1038-bin.002
-rw-rw----  1 mysql mysql   6487 Nov 30 19:57 1038-bin.003
-rw-rw----  1 mysql mysql 116623 Nov 30 20:40 1038-bin.004
-rw-rw----  1 mysql mysql     60 Nov 30 20:26 1038-bin.index
</code>


If you do not specify a name for the log files, the host name (in my case 1038) followed by '-bin' is used. To clarify where these files come from, I have given the log-bin files this name:



<code c>
log-bin=solin01_mysql
</code>



===Deleting binary log files===
To delete a specific range of binary log files, you can issue an SQL command:



<code c>
mysql -uUser -pPassw -e "PURGE MASTER LOGS TO 'solin01_mysql.113'"
</code>


This command will delete all binary log files prior to the mentioned log file. You can also delete the files manually, but then the binary index log file will not be updated, so it is safer to use he SQL command.



There is also an option to delete all binary log files prior to a given date, e.g.:



<code c>
mysql -uUser -pPassw –e "PURGE MASTER LOGS BEFORE now();"
</code>


The ''**before**'' syntax is **not** supported however in version 3.23.58.



To delete all log files, issue this command:



<code c>
mysql -uUser -pPassw –e "RESET MASTER;"
</code>


=====Upgrading MySQL 20060415=====








====Preperations====

===Making Backups Using Webmin or MySQLDump===
First of all, I wanted to make absolutely certain that all databases on the current server can be stored in backup files. So I went to the MySQL section of Webmin, which contains an option to back up all databases. I chose to make backups wich, upon restoring the database, drop the existing tables. All backups are in fact text files, containing SQL commands for creating tables and subsequently inserting the data.

This seems like the safest bet, because making binary backups (if possible at all) may leave you stranded if the binary format of the //next// database server is even slightly different.

Just to be on the safe side, I have also dumped the entire database server:

<code c>
[root@1038 dbs]# mysqldump --all-databases --add-drop-table -u*** -p*** > mysql20060415_1400.sql
</code>


As an added beneift, this dump also includes a //create database //statement.


===Restoring Backups===

Should you need to restore the backups:

<code>
mysql -u USER -p DBNAME < dump.sql
</code>

Please remember to use the correct character encoding. If your database dump was done in UTF8, then you should also tell the mysql client to use UTF8 when restoring the database:

<code>
mysql -u USER -p --default-character-set=utf8 DBNAME < dump.sql
</code>

===Finding out about dependencies===
Using the ''**rpm**'' tool, I quickly came up with these dependencies:

<code c>
[root@1038 root]# rpm -e --test mysql-3.23.58-9
error: Failed dependencies:
        libmysqlclient.so.10 is needed by (installed) perl-DBD-MySQL-2.9003-4
        libmysqlclient.so.10 is needed by (installed) mysql-server-3.23.58-9
        libmysqlclient.so.10 is needed by (installed) libdbi-dbd-mysql-0.6.5-8.1
        mysql = 3.23.58 is needed by (installed) mysql-server-3.23.58-9
        mysql = 3.23.58 is needed by (installed) mysql-devel-3.23.58-9
</code>


I am not overly worried about the consequences of an upgrade for, say, the perl module, because I plan to use either ''**rpm**'' or ''**yum**'' to upgrade. These tools will take care of the dependencies, by (re)installing all  required packages (I hope).




===Keeping the old version ready for emergencies===
From the MySQL website:

//"If you are cautious about using new versions, you can always rename your old ////**mysqld**//// before installing a newer one. For example, if you are using MySQL 4.0.18 and want to upgrade to 4.1.1, rename your current server from ////**mysqld**//// to ////**mysqld-4.0.18**////. If your new ////**mysqld**//// then does something unexpected, you can simply shut it down and restart with your old ////**mysqld**////."//

This seems like a really good idea. This is the location of all mysqld files:

<code c>
[root@1038 root]# find / -name mysqld
/etc/logrotate.d/mysqld
/etc/rc.d/init.d/mysqld
/var/run/mysqld
/var/lock/subsys/mysqld
/usr/libexec/mysqld
</code>


The only thing left to to, is finding out which one is the "real" mysqld.




===The binaries or rpms===
The MySQL people explicitly tell you to use the binaries, instead of compiling your own MySQL server from source code. So, which rpm do we use? There is no official MySQL 4.0 for Fedora Core 2, so we have the following options:

  *using somebody else's Fedora 2 targeted MySQL 4 rpm (assuming we can find one)
  *using the "generic" Linux x86 MySQL 4 rpm
As I was unable to find a specific Fedora 2 targeted MySQL 4 rpm, I decided to go with the generic rpm. You can download the latest 4.0 version (4.0.26) here:

[[http://downloads.mysql.com/archives.php?p=mysql-4.0&v=4.0.26|http://downloads.mysql.com/archives.php?p=mysql-4.0&v=4.0.26]]

Or, more orderly presented, here:

[[http://dev.mysql.com/downloads/mysql/4.0.html|http://dev.mysql.com/downloads/mysql/4.0.html]]

And version 4.1.18 here:

[[http://dev.mysql.com/downloads/mysql/4.1.html|http://dev.mysql.com/downloads/mysql/4.1.html]]






===Shared libraries===
Gotta figure this out yet. See:

[[http://dev.mysql.com/doc/refman/5.0/en/linux-rpm.html|http://dev.mysql.com/doc/refman/5.0/en/linux-rpm.html]]

This is something about MySQL-shared-compat needed for RedHat and therefore, presumably, also for Fedora MySQL installations.

This comes down to installing ''**MySQL-shared-compat-4.0.26-0.i386.rpm**''.




====Upgrade target: from version 3.23.58 to at least MySQL 4.1.16====
I had a very specific reason for upgrading: version 1.6 of the Learning Management System //Moodle //needs at least MySQL 4.1.16. And my version was 3.23.58.

With this goal in mind, I set out to see what MySQL version I should install. After all, the latest MySQL version at the time of writing was 5.1, so I had these versions to choose from: 4.0, 4.1, 5.0 and 5.1. Why not go straight to the latest version? Well, the MySQL website advises:

//"that when you upgrade from one release series to another, you should go to the next series rather than skipping a series. For example, if you currently are running MySQL 4.0 and wish to upgrade to a newer series, upgrade to MySQL 4.1 rather than to 5.0 or 5.1."//

So, getting from 3.23.58 to 5.1 would require me to install //four //subsequent upgrades! Therefore, my target was to first get to at least 4.1.16.




===Upgrading from MySQL 3.23 to 4.0===
Let's get down to business. After upgrading, we need to do what's described here:

[[http://dev.mysql.com/doc/refman/4.1/en/upgrading-from-3-23.html|http://dev.mysql.com/doc/refman/4.1/en/upgrading-from-3-23.html]]

This comes down to:

  *check startup scripts for deprecated stuff
  *convert your old ISAM tables to MyISAM format
  *check dependencies



====The actual upgrade – first attempt====
Enough talk already! Let's just do it now:



<code c>
[root@1038 rpm]# rpm -Uvh --nodeps MySQL-server-4.0.26-0.i386.rpm
warning: MySQL-server-4.0.26-0.i386.rpm: V3 DSA signature: NOKEY, key ID 5072e1f5
Preparing...                ########################################### [100%]
   1:MySQL-server           ########################################### [100%]
Installing all prepared tables
060415 14:15:49 /usr/sbin/mysqld: Shutdown Complete


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 1038.flexservers.com password 'new-password'
See the manual for more instructions.

NOTE:  If you are upgrading from a MySQL <= 3.22.10 you should run
the /usr/bin/mysql_fix_privilege_tables. Otherwise you will not be
able to use the new GRANT command!

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 https://order.mysql.com
</code>






<code c>
[root@1038 rpm]# rpm -Uvh MySQL-shared-compat-4.0.26-0.i386.rpm
warning: MySQL-shared-compat-4.0.26-0.i386.rpm: V3 DSA signature: NOKEY, key ID 5072e1f5
Preparing...                ########################################### [100%]
   1:MySQL-shared-compat    ########################################### [100%]
</code>




<code c>
[root@1038 rpm]# rpm -Uvh MySQL-devel-4.0.26-0.i386.rpm
warning: MySQL-devel-4.0.26-0.i386.rpm: V3 DSA signature: NOKEY, key ID 5072e1f5
Preparing...                ########################################### [100%]
   1:MySQL-devel            ########################################### [100%]
</code>






<code c>
[root@1038 rpm]# rpm -Uvh MySQL-client-4.0.26-0.i386.rpm
warning: MySQL-client-4.0.26-0.i386.rpm: V3 DSA signature: NOKEY, key ID 5072e1f5
Preparing...                ########################################### [100%]
   1:MySQL-client           ########################################### [100%]
</code>


Situation after above installations:



<code c>
[root@1038 rpm]# find / -name mysqld
/var/run/mysqld
/var/lock/subsys/mysqld
/usr/sbin/mysqld
</code>


Unfortunately, ''**/etc/rc.d/init.d/mysqld stop**'''' ''does not work anymore, so I had to reboot the system. I probably should have stopped the MySQL server //before// installing the new version...




===Failure===
After the reboot, the mysqld daemon tells me the following:



<code c>
[root@1038 root]# /usr/sbin/mysqld
Fatal error: Please read "Security" section of the manual to find out how to run                                              mysqld as root!
060415 14:37:27 Aborting

060415 14:37:28 /usr/sbin/mysqld: Shutdown Complete
</code>




A web search suggests that I use ''**mysqld_safe**'':



<code c>
[root@1038 bin]# ./mysqld_safe start
chown: `mysql': invalid user
Starting mysqld daemon with databases from /var/lib/mysql
STOPPING server from pid file /var/run/mysqld/mysqld.pid
060415 14:46:52  mysqld ended
</code>


Close. But apparently the mysql user has been deleted. So:



<code c>
[root@1038 bin]# groupadd mysql
[root@1038 bin]# useradd -g mysql mysql

</code>


But unfortunately:



<code c>
[root@1038 bin]# ./mysqld_safe start
Starting mysqld daemon with databases from /var/lib/mysql
STOPPING server from pid file /var/run/mysqld/mysqld.pid
060415 14:53:49  mysqld ended
</code>









====Plunging forward – successfully – to 4.1.18====
Nothing really worked, so I decided to just skip the 4.0 version.



<code c>
[root@1038 rpm]# rpm -Uvh --nosignature --nodeps MySQL-server-4.1.18-0.i386.rpm
Preparing...                ########################################### [100%]
Giving mysqld a couple of seconds to exit nicely
   1:MySQL-server           ########################################### [100%]
060415 15:48:01 [Warning] Asked for 196608 thread stack, but got 126976
060415 15:48:01 [Warning] Asked for 196608 thread stack, but got 126976
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 1038.flexservers.com password 'new-password'
See the manual for more instructions.

NOTE:  If you are upgrading from a MySQL <= 3.22.10 you should run
the /usr/bin/mysql_fix_privilege_tables. Otherwise you will not be
able to use the new GRANT command!

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 https://order.mysql.com
Starting MySQL. SUCCESS!
</code>


And the other rpms:



<code c>
[root@1038 rpm]# rpm -Uvh --nosignature MySQL-client-4.1.18-0.i386.rpm
Preparing...                ########################################### [100%]
   1:MySQL-client           ########################################### [100%]
[root@1038 rpm]# rpm -Uvh --nosignature MySQL-devel-4.1.18-0.i386.rpm
Preparing...                ########################################### [100%]
   1:MySQL-devel            ########################################### [100%]
[root@1038 rpm]# rpm -Uvh --nosignature MySQL-shared-compat-4.1.18-0.i386.rpm
Preparing...                ########################################### [100%]
   1:MySQL-shared-compat    ########################################### [100%]
</code>



===Warning===
The MySQL server is up and running again, but the rpm manager does not seem to know it, even though we installed everything //through //rpm!

<code c>
[root@1038 rpm]# rpm -q mysql
package mysql is not installed
[root@1038 rpm]# rpm -q MySQL-server-4.1.18-0.i386.rpm
package MySQL-server-4.1.18-0.i386.rpm is not installed
</code>



===MyIsam===


//"The //''//ISAM//''// file format still works in MySQL 4.0, but is deprecated and is not compiled in by default as of MySQL 4.1. //''//MyISAM//''// tables should be used instead."//

([[http://dev.mysql.com/doc/refman/4.1/en/upgrading-from-3-23.html|http://dev.mysql.com/doc/refman/4.1/en/upgrading-from-3-23.html]])



I have checked a few random tables in databases created (originally on MySQL 3.23) by Moodle and Typo3. All were of type MyIsam. Us phpMyAdmin to check this, or fire up the mysql client, connect to a specific database, and issue the following statement:

<code c>
show table status like 'mdl_assignment'
</code>





====Starting and Stopping MySQL 20060415====
Apparently, you can start and stop MySQL through:

<code c>
/usr/bin/mysqld_safe --user=mysql &
/usr/bin/mysqladmin -u root shutdown
</code>


I have also used these commands in the Webmin MySQL module.






====Performance issues in MySQL====
Ever since I have installed MySQL 4.1, Moodle is having performance related issues. If more than 20 users log on, the DB server halts. Here are some locations and tools for monitoring MySQL performance. The thing is, I am not really sure if the previous DB installation did not have the same problems: maybe there just weren't that many simultaneous users yet.

The file ''**/etc/my.cnf**'' is used to set MySQL variables. Here is mine (20061002):



<code c>
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Uncomment the following if you want to log updates
log-bin=/var/lib/mysql/backups/solin01_mysql
log_slow_queries=/var/log/mysql_slow_queries.log
long_query_time=10
log=/var/log/mysql_general.log
log-warnings=2
set-variable=max_connections=500
     
[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
</code>


Note that you should **not **normally log all general MySQL activity, as this log will enlarge very quickly. At  the very least, this log should be rotated. General logging corresponds to the ''**mysqladmin variables**'' setting:

<code c>
| log                             | ON                                      |
</code>


You can see what variables are currently in use by issueing the following command (username and password omitted here): ''**mysqladmin variables**''. Here's a snippet of output:



<code c>
| log                             | OFF                                      |
| log_bin                         | ON                                       |
| log_error                       |                                          |
| log_slave_updates               | OFF                                      |
| log_slow_queries                | ON                                       |
| log_update                      | OFF                                      |
| log_warnings                    | 2                                        |
| long_query_time                 | 10                                       |
</code>







=====MySQL 5=====


After running apt-get install mysql-server, a Debian configuration tool came up with the following notice:

<code c>
Install Hints

On upgrades from MySQL 3.23, as shipped with Debian Woody, symlinks in place of /var/lib/mysql or /var/log/mysql gets accidently removed and have manually be restored.

MySQL will only install if you have a non-numeric hostname that is resolvable via the /etc/hosts file. E.g. if the "hostname" command returns "myhostname" then there must be a line like "10.0.0.1 myhostname".                               
A new mysql user "debian-sys-maint" will be created. This mysql account is used in the start/stop and cron scripts. Don't delete.

Please remember to set a PASSWORD for the MySQL root user! If you use a /root/.my.cnf, always write the "user" and the "password" lines in there, never only the password!

See /usr/share/doc/mysql-server-5.0/README.Debian for more information.
</code>


The ''**debian-sys-maint**'' user does not seem to exist as a unix user, however, but solely as a MySQL user:

<code c>
host:/etc/apt# passwd debian-sys-maint
passwd: Unknown user debian-sys-maint
</code>




The whole installation output was:

<code c>
host:/etc/apt# apt-get install mysql-server
Reading Package Lists... Done
Building Dependency Tree... Done
The following extra packages will be installed:
  libmysqlclient15off mysql-client-5.0 mysql-common mysql-server-5.0
Suggested packages:
  tinyca
The following packages will be REMOVED:
  mysql-client-4.1 mysql-common-4.1 mysql-server-4.1
The following NEW packages will be installed:
  libmysqlclient15off mysql-client-5.0 mysql-common mysql-server mysql-server-5.0
0 upgraded, 5 newly installed, 3 to remove and 28 not upgraded.
Need to get 30.6MB of archives.
After unpacking 37.1MB of additional disk space will be used.
Do you want to continue? [Y/n] Y
Get:1 http://dotdeb.netmirror.org stable/all mysql-common 5.0.24a-2.dotdeb.0 [40.8kB]
Get:2 http://dotdeb.netmirror.org stable/all libmysqlclient15off 5.0.24a-2.dotdeb.0 [1747kB]
Get:3 http://dotdeb.netmirror.org stable/all mysql-client-5.0 5.0.24a-2.dotdeb.0 [6950kB]
Get:4 http://dotdeb.netmirror.org stable/all mysql-server-5.0 5.0.24a-2.dotdeb.0 [21.8MB]
Get:5 http://dotdeb.netmirror.org stable/all mysql-server 5.0.24a-2.dotdeb.0 [38.1kB]
Fetched 30.6MB in 9s (3065kB/s)
Preconfiguring packages ...
(Reading database ... 16272 files and directories currently installed.)
Removing mysql-server-4.1 ...
Stopping MySQL database server: mysqld.
Removing mysql-client-4.1 ...
dpkg: mysql-common-4.1: dependency problems, but removing anyway as you request:
 libmysqlclient12 depends on mysql-common (>= 4.0.24-10sarge2) | mysql-common-4.1; however:
  Package mysql-common is not installed.
  Package mysql-common-4.1 is to be removed.
 libmysqlclient14 depends on mysql-common-4.1 (>= 4.1.11a-4sarge5) | mysql-common; however:
  Package mysql-common-4.1 is to be removed.
  Package mysql-common is not installed.
  Package mysql-common-4.1 which provides mysql-common is to be removed.
 libmysqlclient12 depends on mysql-common (>= 4.0.24-10sarge2) | mysql-common-4.1; however:
  Package mysql-common is not installed.
  Package mysql-common-4.1 is to be removed.
 libmysqlclient14 depends on mysql-common-4.1 (>= 4.1.11a-4sarge5) | mysql-common; however:
  Package mysql-common-4.1 is to be removed.
  Package mysql-common is not installed.
  Package mysql-common-4.1 which provides mysql-common is to be removed.
Removing mysql-common-4.1 ...
Selecting previously deselected package mysql-common.
(Reading database ... 16040 files and directories currently installed.)
Unpacking mysql-common (from .../mysql-common_5.0.24a-2.dotdeb.0_all.deb) ...
Selecting previously deselected package libmysqlclient15off.
Unpacking libmysqlclient15off (from .../libmysqlclient15off_5.0.24a-2.dotdeb.0_i386.deb) ...
Selecting previously deselected package mysql-client-5.0.
Unpacking mysql-client-5.0 (from .../mysql-client-5.0_5.0.24a-2.dotdeb.0_i386.deb) ...
Selecting previously deselected package mysql-server-5.0.
Unpacking mysql-server-5.0 (from .../mysql-server-5.0_5.0.24a-2.dotdeb.0_i386.deb) ...
Selecting previously deselected package mysql-server.
Unpacking mysql-server (from .../mysql-server_5.0.24a-2.dotdeb.0_all.deb) ...
Setting up mysql-common (5.0.24a-2.dotdeb.0) ...
Installing new version of config file /etc/mysql/my.cnf ...
Setting up libmysqlclient15off (5.0.24a-2.dotdeb.0) ...

Setting up mysql-client-5.0 (5.0.24a-2.dotdeb.0) ...
Setting up mysql-server-5.0 (5.0.24a-2.dotdeb.0) ...
Installing new version of config file /etc/init.d/mysql ...
Installing new version of config file /etc/logrotate.d/mysql-server ...
Installing new version of config file /etc/mysql/debian-start ...
Stopping MySQL database server: mysqld.
Starting MySQL database server: mysqld.
Checking for corrupt, not cleanly closed and upgrade needing tables.

Setting up mysql-server (5.0.24a-2.dotdeb.0) ...
</code>





====Configuration====
The user table held the following information:

<code c>
mysql> select User, Password from user;
+------------------+------------------+
| User             | Password         |
+------------------+------------------+
| root             |                  |
| root             |                  |
| debian-sys-maint | *******          |
+------------------+------------------+
</code>


My first job was to assign a password to the root user:

<code c>
host:/etc/mysql# mysqladmin -u root password newpwd
</code>





=====Reset MySQL Root Password=====

Not that you would ever forget your MySQL root password of course, but here's how to reset it.

[[http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html|How to Reset the Root Password]]. This web page explains two methods. I couldn't get the first one to work, so I'll only document the second one here.


Stop your MySQL server. To do this, find out where your .pid file is located and put the path in the following command (and **do** use these backticks, or it will not work):
<code>
host:~# kill  `cat /var/run/mysqld/mysqld.pid`
</code>

Now, restart the webserver with this command:
<code>
host:~# mysqld_safe --skip-grant-tables --user=root
</code>

And log in as root:
<code>
host:~# mysql -u root
</code>

Finally, just issue the SQL commands to reset the password:
<code>
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.30-Debian_0.dotdeb.1 Dotdeb Sarge backport

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> UPDATE mysql.user SET Password=PASSWORD('NewPassword') WHERE User='root';
Query OK, 2 rows affected (0.03 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
</code>


=====Import Script for GZipped SQL Files=====
<code>
#!/bin/bash
db=$1
user=$2
passwd=$3
if [ "$db" = "" ]; then
  echo "Usage: $0 db_name user password"
  exit 1
fi
clear
for sql_file in *.sql.gz; do
  echo "Importing $sql_file";
  zcat $sql_file | mysql --user=$user --password=$passwd $db
done 
</code>
This script takes all *.sql.gz files in the current directory, unzips them (with zcat) to the standard output, which is redirected (with the pipe symbol |) to the mysql client.

The original files are kept in place (i.e. unzipped).
You are here: start » linux » mysql
Views

Navigation

Personal Tools

Search

Toolbox
