Archive for March, 2007

The other day I destroyed by Mysql installation by erasing the required databases (mysql database, privileges database). My first decision was to try and uninstall and reinstall a fresh copy. This will not immediately work; during an uninstall of the files it still will not remove the database files. Mysql databases are stored at “/var/lib/mysql/”. Go to this folder to backup any databases that you were using for your web programs, rename the folder to something link “mysql(backup). After doing this a reinstall of all Mysql and dependency files will work, including a removal of the root password. You will have to reset the root password again with the following command mysqladmin -u root -p password ‘………’ the command actually has the single quotation marks that you put your password within (where the dots/periods are). After a clean install, and setting the root password, go into your backed up mysql folder and copy the databases that you want to recover into the new mysql folder. BAM your back up and running. Now just login via PHPMyadmin with “root” and your new password and you will see all your old databases and all your accidentally deleted required databases. You can add users by the PHPMyadmin “privileges” button available in the main column. If you ever have any trouble access Mysql, such as a “sock” error, its your my.cnf file stored in /etc/my.cnf . Give a Google search for default my.cnf file and copy paste the test into your cnf file in the /etc/ directory. There is a local copy of the default .cnf file located on your computer in the “doc” folder. In addition the following is a basic my.cnf file for so called “small systems”.

# Example mysql config file for small systems.
#
#
# You can copy this file to
# /etc/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is /usr/local/mysql/var) or
# ~/.my.cnf to set user-specific options.
#

# The following options will be passed to all MySQL clients
[client]
#password       = your_password
port            = 3306
socket          = /tmp/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port            = 3306
socket          = /tmp/mysql.sock
skip-locking
set-variable    = key_buffer=128K
set-variable    = max_allowed_packet=1M
set-variable    = thread_stack=64K
set-variable    = table_cache=4
set-variable    = sort_buffer=64K
set-variable    = net_buffer_length=2K
server-id       = 1

# Uncomment the following if you want to log updates
#log-bin

# Uncomment the following if you are NOT using BDB tables
#skip-bdb

# Uncomment the following if you are using Innobase tables
#innodb_data_file_path = ibdata1:100M
#innodb_data_home_dir = /usr/local/mysql/var/
#innodb_log_group_home_dir = /usr/local/mysql/var/
#innodb_log_arch_dir = /usr/local/mysql/var/
#set-variable = innodb_mirrored_log_groups=1
#set-variable = innodb_log_files_in_group=3
#set-variable = innodb_log_file_size=5M
#set-variable = innodb_log_buffer_size=8M
#innodb_flush_log_at_trx_commit=1
#innodb_log_archive=0
#set-variable = innodb_buffer_pool_size=16M
#set-variable = innodb_additional_mem_pool_size=2M
#set-variable = innodb_file_io_threads=4
#set-variable = innodb_lock_wait_timeout=50

[mysqldump]
quick
set-variable    = max_allowed_packet=16M

[mysql]
no-auto-rehash
#safe-updates

[isamchk]
set-variable    = key_buffer=8M
set-variable    = sort_buffer=8M

[myisamchk]
set-variable    = key_buffer=8M
set-variable    = sort_buffer=8M