Frank Schacherer Homepage
home technology bibliography

MySQL

[Angel.]

MySQL is free and fast. This is my scrapbook of useful statements. You can find a more thorough treatment in the MySQL Documentation

Installing and kicking the tires

What files are installed, and where? There are:

You can get the dirs for database and character-sets from mysqld --help. The above values were the initial ones for my rpm-installation.

To move the databases, you have to physically move the files to the new location. Make sure they are owned by the mysql user. You also have to tell mysqld where to look for them.

You start the daemeon with safe_mysqld --log &. This is a shell script which sets the default for DATADIR. Although advised against it, I just edited the default to the new location, and it worked fine. You can probably also give the dir as an option. Look at all options with grep '\-\-', or look at the doc, where they are explained, too.

The client programs need a socket file, which is also stored in the datadir. I installed a global config file /etc/my.cnf to tell them wher to find it:

[client]
port=3306
socket=/mysql/mysql.sock

[mysqldump]
quick

mysqladmin is a tool for administering your server.Test if it is running with mysqladmin ping. See variables with mysqladmin variables (this gives what show variables gives). Then shut it down with mysqladmin shutdown. Restart as above.

Connecting

mysql -h host -u user -p

Often a simple mysql on the host machine running mysqld will suffice to connect you.

mysql is the text based client for working on your server. Since all the admin info is stored in the mysql database on your server, you can also administrate it by manipulating that database directly in mysql. Some important options, which are alos used by the other tools like mysqladmin:

Account management

MySQL account information is stored in the tables of the mysql database.

Creating accounts

Per default, anyone can log in from the daemons host machine as root with all rights. There are two users created by mysql_install_db, a script you either should run after setting up, or that is run automatically (during rpm install): root and the anonymous user without name.

Although you can create accounts with create user username [identified by 'password'] and drop them again by drop user username, issuing a grant statement will imlicitly create the user, so you can safe that step.

MySQL assigns rights and passwords to users@machines, that means the same user may have different rights depending from where he logged in. There are four ways to assign passwords:

  1. Using mysqladmin. mysqladmin -u root -h host password 'new password'. This sets a new password for root at host. Note that, as soon as you have a password, you need to use -p to connect from that host. You usually do this for root on localhost as soon as the db is up and running.
  2. When creating the user in mysql, e.g. grant all on *.* to monty@host identified by 'passwd' with grant option;.

    Here ALL grants all privileges. When you just want to create the user and grant connect privilege, use USAGE instead. *.* refers to all databases - you can select the databases by name instead. If you drop the @-part in the user id, the host will be set to '%', standing for any host. If you drop the identified by clause, there is no initial password, if you drop the with grant option, the user can't grant stuff to others.

    Note that we must issue GRANT statements for both monty@localhost and monty@"%". If we don't add the entry with localhost, the anonymous user entry for localhost that is created by mysql_install_db will take precedence when we connect from the local host, because it has a more specific host field value and thus comes earlier in the user table sort order.

  3. With SET PASSWORD FOR monty@"%" = PASSWORD('biscuit');. Here the user already exists. Any user can set his own password by doing set password = password('mypw'). Note that you have to explicitly encrypt the password with PASSWORD(). This is pretty useless syntactic clutter for...
  4. Updating the tables in mysql.user: UPDATE user SET Password=PASSWORD('new_password') WHERE user='user name'; flush privileges;. Grant automatically flushes the privileges (reloading them, so the server actually uses them), updating the tables by hand does not, and you have to do manually with flush privileges; .

Backup and Recovery


mysqldump

mysqldump --opt database > backup-file.sql
You can read this back into MySQL with: 

mysql database < backup-file.sql
show process list

Server Optimization

mysqlshow

quit

/usr/local/mysqlcc

perror

mysql -u root -p mysql login to mysql prompting for pw

>mysqladmin -p -u root shutdown
The system will prompt you to enter the password.

Info about your setup

show processlist; show all processes running
show variables;   show all use variables
show status;      show usage statistics;
show databases;
use mydb; # change to mydb
show tables;

set varname=value
describe table;
select * from tab limit 7;
select user();
SELECT * FROM pet WHERE name REGEXP "^b";



Emacs as client

;; emacs
;; Make mysql not buffer sending stuff to the emacs-subprocess-pipes
;; -n unbuffered -B batch(tab separated) -f force(go on after error) -i ignore spaces -q no caching -t table format 
(setq-default sql-mysql-options (quote ("-n" "-B" "-f" "-i" "-q" "-t")))

Reference

MySQL Home Page