Skip to content

mySQL tricks

 Setting Up the Initial MySQL Privileges

After installing MySQL, you set up the initial access privileges by running scripts/mysql_install_db. The mysql_install_db script starts up the mysqld server, then initializes the grant tables to contain the following set of privileges:

The MySQL root user is created as a superuser who can do anything.  Connections must be made for the local hose.  Note:  The initial root password is empty, so anyone can connect as root without a password and be granted all privileges.
An anonymous user is created that can do anything with databases that have a name of ‘test’ or starting with ‘test_’.

Connections must be made from the local host.  This means any local user can connect without a password and be treated as the anonymous user.  Because your installation is initially wide open, one of the first things you should do is specify a password for the MySQL root user.  You can do this as follows (note that you specify the password using the PASSWORD () function):

shell> mysql -u root mysql
mysql> SET PASSWORD FOR root@localhost=PASSWORD (‘xxxx’);

or:

shell> mysqladmin --user=root password ‘xxxx’
shell> mysqladmin --user=root password ‘’

 Post-installation Setup and Testing

Use mysqladmin to verify that the server is running.  The following commands provide a simple test to check that the server is up and responding to connections:

shell> mysqladmin version --user=root --password=xxxx

msqladmin Ver 8.40 Distrib 4.0.15, for pc-linux on i686
Copyright © 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTLEY NO WARRANTY.  This is free software, and you are welcome to modify and redistribute is under the GPL license

Server version 4.0.15-standard-log
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mtsql.sock
Uptime: 60 hours 45 min 39 sec

Shell> mysqladmin processlist –user=root — password=xxxx

~$ mysqladmin processlist –user=cordinc –password=xxxxxxxx
+———+———+———–+—-+———+——+——-+——————+
| Id      | User    | Host      | db | Command | Time | State | Info             |
+———+———+———–+—-+———+——+——-+——————+
| 1219584 | cordinc | localhost |    | Query   | 0    |       | show processlist |
+———+———+———–+—-+———+——+——-+——————+

shell> mysqladmin variables –user=root  –password=xxxx
cordinc@kilo:~$ mysqladmin variables –user=cordinc –password=xxxxxxxxx
+—————————————–+—————————–+
| Variable_name                           | Value                       |
+—————————————–+—————————–+
| auto_increment_increment                | 1                           |
| auto_increment_offset                   | 1                           |
| automatic_sp_privileges                 | ON                          |
| back_log                                | 50                          |
| basedir                                 | /usr/                       |
| binlog_cache_size                       | 32768                       |

shell> mysqlshow –user=root –password=xxxx

cordinc@kilo:~$ mysqlshow –user=cordinc –password=xxxxxxx
+——————–+
|     Databases      |
+——————–+
| information_schema |
| cordinc            |
+——————–+

cordinc@kilo:~$ mysqlshow –user=cordinc –password=xxxxxxx cordinc
Database: cordinc
+———————————-+
|              Tables              |
+———————————-+
| jos_agora_adsense_config         |
| jos_agora_bans                   |
| jos_agora_bans_auto              |
| jos_agora_categories             |
| jos_agora_censoring              |
| jos_agora_config                 |
| jos_agora_feeds                  |
| jos_agora_forums                 |

shell> mysql –user=root –password=xxxx -e “SELECT host,db,user FROM db” mysql

OR

pete@thome:~$ mysql –user=root -p -e “SELECT host,db,user FROM db” mysql
Enter password:
+———————–+—————+—————-+
| host                  | db            | user           |
+———————–+—————+—————-+
| %                     | kel_wiki      | kel_mysql_user |
| %                     | kel_wordpress | kel_mysql_user |
| localhost             | kel_wiki      | kel_mysql_user |
| localhost.localdomain | kel_wiki      | kel_mysql_user |
+———————–+—————+—————-+

There is also a benchmark suite in the ‘sql-bench’ directory (under the MySQL installation directory) that you can use to compare how MySQL performs on different platforms.  The benchmark suite is written in Perl, using the Perl DBI module to provide a database-independent interface to the various databases.  The following additional Perl modules are required to run the benchmark suite:

DBI
DBD-mysql
Data-Dumper
Data-ShowTable

shell> ./run-all-tests -user=root -password=xxxx

Database Administration

Exporting and Importing into and from ASCII Files using Load Data

Pulling data from MySQL into an external, ASCII File:

USE bugs;

mysql>     SELECT * INTO OUTFILE ‘users.dat’
FIELDS OPTIONALLY ENCLOSED BY ‘”’ TERMINATED BY ‘;’
FROM profiles;

Importing the external file:

mysql>    CREATE DATABASE martin;
mysql>    use martin;

mysql>    CREATE TABLE profiles (
->    userid mediumint (9) NOT NULL  auto_increment,
->    login_name varchar (255) NOT NULL default ‘‘,
->    cryptpassword varchar (34) default NULL,
->    groupset bigint (20) NOT NULL default ‘0’,
->    disabledtaxt mediumtext NOT NULL,
->    mybugslink tinyint (4) NOT NULL default ‘1’,
->    blessgroupset bigint (20) NOT NULL default ‘0’,
->    emailflags mediumtext,
->    PRIMARY KEY (userid),
->    UNIQUE KEY login_name (login_name)
->  ) TYPE=InnoDB;

mysql> desc profiles;

mysql>       LOAD DATA LOCAL INFILE ‘users.dat’
->    INTO TALBE PROFILES
->    FIELDS OPTIONALLY ENCLOSED BY ‘”‘ TERMINATED BY ‘;‘;

Exporting and Importing using Msqldump

mysqldump –user=root  –password=xxx –opt bugs > bugs.sql
mysql> create database bugs;
mysql> exit;
mysql> –user=root –password=xxx bugs < bugs.sql

Adding New users to MySQL

You can add users two different ways:  by using GRANT statements or by manipulating the MySQL grant tables directly.  The preferred method is to use GRANT statements, because they are more concise and less error-prone.

First, use the mysql program to connect to the server as the MySQL root user:

shell> mysql –user=root –password=xxxx mysql

Then you can add new users by issuing GRANT statements:

mysql>    GRANT ALL PRIVILEGES ON *.* TO bugs@localhost
IDENTIFIED BY ‘some_pass’ WITH GRANT OPTION;

Mysql>    GRANT ALL PRIVILEGES ON *.* TO bugs@’%’
IDENTIFIED BY ‘some_pass’ WITH GRANT OPTION;

Recovering from Password Problems

If you garble your GRANT commands or forget passwords and find that you don’t have access to the critical mysql table – even as the root user – don’t panic.  Become the superuser on the operating system (e.g. the UNIX root, not the MySQL root) and kill the MySQL process.  On a RedHat Linux system, you might be able to end MySQL through the command:
shell> /etc/rc.d/init.d/mysql stop

Now start uo MySQL again, bypassing the grant tables and assign a new password for the MySQL root yser:

shell> cd /usr/local/mysql/bin
shell> ./mysqld_safe –skip-grant-tables 1>/dev/null 2>&1 &
shell> mysql -u root
mysql> use mysql
mysql> UPDATE user SET Password=PASSWORD(‘newpassword’) WHERE user=’root’;
mysql> exit;

Now, find all MySQL Processes and kill them explicitly as root:

shell> ps ax | grep mysql
shell> kill xxxx  or killall mysql

Now, you can start MySQL again with the normal startup parameters, the password is now changed:

shell> /etc/rc.d/init.d/mysqld start

Database Backups

Because MySQL tables are stored as files, it is easy to do a backup.

mysqldump –user=root –password=xxxx –opt mysql > mysql.sql
mysqldump –user=root –password=xxxx –quick mysql > mysql.dump
mysqlhotcopy –user=root –password=xxxx –allowold –keepold mysql /home/zahn/backup

This is from a presentation that Solomon Chang gave some years back at an SGLUG meeting.  Pretty clever dude.

Post a Comment

Your email is never published nor shared. Required fields are marked *