MySQL

My notes about using mysql.

MySQL is *REALLY* picky about domain names.  So if you change the name of your machine (or change domains or add a domain name) you will get looked out of your mysql server!  So be REALLY careful when you are changing names.

Using IP addresses Only

Added the 'skip-name-resolve' option to the /etc/mysql/my.cnf so it will not resolve ip address to names.  This was an issue because the machine the connection is comming from is a web server, to it will have A*LOT of names.

http://dev.mysql.com/doc/refman/5.0/en/dns.html

http://lists.mysql.com/mysql/180963

Loging of user/connectivity

Turned on logging to /var/log/mysql.log in the /etc/mysql/my.cnf

http://www.washington.edu/computing/web/publishing/mysql-admin.html#logs

http://dev.mysql.com/doc/refman/5.0/en/option-files.html

User Managment

Setting the administrator password

Set the admin password from the root account command line, in linux of course. (replace "my_password", with the password)

mysql -u root -e "set password = password('my_password');"

Creating a User by altering the mysql.user table

( Used to create user in CiviSpace mysql db)

INSERT INTO user
VALUES('%','civicsuser', 'password', 1,           2,          3,

'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y',
 4,  5,  6 , 7 , 8 , 9 , 10, 11, 12, 13, 14, 15, 16, 17

'Y','N','Y','Y','Y','N','N', , ,   , ,  0,  0, 0);
 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31

  +-----------------------+-----------------------------------+------+-----+---------+-------+
  | Field                 | Type                              | Null | Key | Default | Extra |
  +-----------------------+-----------------------------------+------+-----+---------+-------+
01| Host                  | char(60)                          | NO   | PRI | NULL    |       |
02| User                  | char(16)                          | NO   | PRI | NULL    |       |
03| Password              | char(41)                          | NO   |     | NULL    |       |
04| Select_priv           | enum('N','Y')                     | NO   |     | N       |       |
05| Insert_priv           | enum('N','Y')                     | NO   |     | N       |       |
06| Update_priv           | enum('N','Y')                     | NO   |     | N       |       |
07| Delete_priv           | enum('N','Y')                     | NO   |     | N       |       |
08| Create_priv           | enum('N','Y')                     | NO   |     | N       |       |
09| Drop_priv             | enum('N','Y')                     | NO   |     | N       |       |
10| Reload_priv           | enum('N','Y')                     | NO   |     | N       |       |
11| Shutdown_priv         | enum('N','Y')                     | NO   |     | N       |       |
12| Process_priv          | enum('N','Y')                     | NO   |     | N       |       |
13| File_priv             | enum('N','Y')                     | NO   |     | N       |       |
14| Grant_priv            | enum('N','Y')                     | NO   |     | N       |       |
15| References_priv       | enum('N','Y')                     | NO   |     | N       |       |
16| Index_priv            | enum('N','Y')                     | NO   |     | N       |       |
17| Alter_priv            | enum('N','Y')                     | NO   |     | N       |       |
18| Show_db_priv          | enum('N','Y')                     | NO   |     | N       |       |
19| Super_priv            | enum('N','Y')                     | NO   |     | N       |       |
20| Create_tmp_table_priv | enum('N','Y')                     | NO   |     | N       |       |
21| Lock_tables_priv      | enum('N','Y')                     | NO   |     | N       |       |
22| Execute_priv          | enum('N','Y')                     | NO   |     | N       |       |
23| Repl_slave_priv       | enum('N','Y')                     | NO   |     | N       |       |
24| Repl_client_priv      | enum('N','Y')                     | NO   |     | N       |       |
25| Create_view_priv      | enum('N','Y')                     | NO   |     | N       |       |
26| Show_view_priv        | enum('N','Y')                     | NO   |     | N       |       |
27| Create_routine_priv   | enum('N','Y')                     | NO   |     | N       |       |
28| Alter_routine_priv    | enum('N','Y')                     | NO   |     | N       |       |
29| Create_user_priv      | enum('N','Y')                     | NO   |     | N       |       |
30| ssl_type              | enum(,'ANY','X509','SPECIFIED')   | NO   |     | NULL    |       |
31| ssl_cipher            | blob                              | NO   |     | NULL    |       |
32| x509_issuer           | blob                              | NO   |     | NULL    |       |
33| x509_subject          | blob                              | NO   |     | NULL    |       |
34| max_questions         | int(11) unsigned                  | NO   |     | 0       |       |
35| max_updates           | int(11) unsigned                  | NO   |     | 0       |       |
36| max_connections       | int(11) unsigned                  | NO   |     | 0       |       |
37| max_user_connections  | int(11) unsigned                  | NO   |     | 0       |       |
  +-----------------------+-----------------------------------+------+-----+---------+-------+

mysql> INSERT INTO user

    ->     VALUES('%','monty',PASSWORD('some_pass'),

    ->     'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');

mysql> INSERT INTO user SET Host='localhost',User='admin',

    ->     Reload_priv='Y', Process_priv='Y';

mysql> INSERT INTO user (Host,User,Password)

    ->     VALUES('localhost','dummy',);mysql> FLUSH PRIVILEGES;

UPDATE user SET password=PASSWORD('something') WHERE user = 'monty';

could of used the grant privliages

mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost'

    ->     IDENTIFIED BY 'some_pass' WITH GRANT OPTION;

mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%'

    ->     IDENTIFIED BY 'some_pass' WITH GRANT OPTION;

mysql> GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost';

mysql> GRANT USAGE ON *.* TO 'dummy'@'localhost';

Can use grant command to create user.

GRANT ALL PRIBILEGES ON phpbbdb.* TO 'phpbb'@'localhost' IDENTIFIED BY 'Wha7Scrt4';

MySQL 4.1 How to create a user link

http://dev.mysql.com/doc/refman/4.1/en/adding-users.html

An example user add. (adding users who can just see values).

mysql> insert into user (Host,User,Password) VALUES ( '10.16.10.136', 'metro', 'password ');
Query OK, 1 row affected, 3 warnings (0.00 sec)

mysql> update user set Select_Priv='y'  where user='metro';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

*NOTE* with version 5.0 use to the PASSWORD() function when adding a password to a user.  To find this out I only saw an error when the database server started, and it put a message in the log.

*NOTE* with version 5.0 I also had to use the GRANT command to allow access to the databse.  (? Not sure if you must also do this in 4 ? )

Make sure the privileges take effect.

flush PRIVILEGES;

Creating a user

User the create user command to create the user, then alter the privileges.

create user fredsql;

 update mysql.user set select_priv='Y', insert_priv='y', update_priv='y',delete_priv='y',create_priv='y',alter_priv='y' where user = 'fredsql';

You may also want to set the users password now,

use mysql;
update user set password=PASSWORD('ANewSecurePassword') where user='fredsql';

If you have granted all privilages you may want to take these away from a user.

revoke CREATE USER, PROCESS, SHUTDOWN, SUPER, GRANT OPTION on *.* from 'fredsql'@'localhost';

Make sure the privileges take effect.

flush PRIVILEGES;

Admin password Recovery

Ok, there is no real recovery, you can just reset the password.

Stop the server

sudo /etc/init.d/mysql stop

Then start the server by hand, with the option to ingnore the server security

sudo mysqld_safe --skip-grant-tables &

Then login to the mysql server without a password

mysql -u root

Then reset the password via sql

root@testtest:~# mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.0.51a-3ubuntu5.4 (Ubuntu)

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

mysql> select user,host,password from  mysql.user;
+------------------+-----------+-------------------------------------------+
| user             | host      | password                                  |
+------------------+-----------+-------------------------------------------+
| root             | localhost | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA | 
| root             | testtest  | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA | 
| root             | 127.0.0.1 | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA | 
|                  | localhost |                                           | 
|                  | testtest  |                                           | 
| debian-sys-maint | localhost | *A55451DA225FD5D701EAB76B17AA900BF3ADCB28 | 
+------------------+-----------+-------------------------------------------+
6 rows in set (0.00 sec)

mysql> 
mysql> update mysql.user set password=PASSWORD('mysql') where user='root';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 3  Changed: 0  Warnings: 0

mysql> 
mysql> select user,host,password from  mysql.user;
+------------------+-----------+-------------------------------------------+
| user             | host      | password                                  |
+------------------+-----------+-------------------------------------------+
| root             | localhost | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA | 
| root             | testtest  | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA | 
| root             | 127.0.0.1 | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA | 
|                  | localhost |                                           | 
|                  | testtest  |                                           | 
| debian-sys-maint | localhost | *A55451DA225FD5D701EAB76B17AA900BF3ADCB28 | 
+------------------+-----------+-------------------------------------------+
6 rows in set (0.00 sec)

mysql> 
mysql> \q
Bye

Then stop the server

sudo /etc/init.d/mysql stop

Then you should be done, netxt time you restart your server the password has been reset to what you want.

Links

http://webyog.com/faq/23_36_en.html

http://dev.mysql.com/doc/refman/5.0/en/connection-access.html

http://dev.mysql.com/doc/refman/5.0/en/grant.html

http://dev.mysql.com/doc/refman/5.0/en/adding-users.html

http://www.cyberciti.biz/tips/recover-mysql-root-password.html

Good link about mysql system tables for privlages and users

http://www.idevelopment.info/data/MySQL/DBA_tips/Security/SEC_1.shtml

Mysql Grant Syntax

http://dev.mysql.com/doc/refman/5.0/en/grant.html

Useful Commands

To see all databases

  use mysql;

  select * from db;

  select Db from db;

or

  show databases

To Show tables

  show tables;

To See info about the table

  describe {tablename};

To use a database

use calld

Database Backup/Recovery

This is kind of primative, but works

mysqldump -u root -p  mysqldb > afile.text

/usr/local/mysql/bin/mysqldump -u root -p --add-locks --extended-insert --all-databases > database_dump

and try adding "--add-drop-database", only work on mysql db version 4.1.31

http://www.linuxcommand.org/man_pages/mysqldump1.html

To recover from this file, I needed to delete the first part of the file, it is info about the mysql initernal database.  Then I was able to load the rest of the file and thus all databases.

For example, here is the begining of the dump file.  You would need to start delete at the "CREATE DATABASE /*!32312 IF NOT EXISTS*/ mysql;" (including this line) line.

-- MySQL dump 9.11
--
-- Host: localhost    Database:
-- ------------------------------------------------------
-- Server version       4.0.20-standard

--
-- Current Database: mysql
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ mysql;

USE mysql;

Delete up to the next, but not including the next "CREATE DATABASE" statement.

--
-- Current Database: adatabase
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ adatabase;

Then save the file, and you should be able to use the following statement to load the database in.

mysql < the_data_base_dump_file.mysql

Here is a VI Macro to create a drop line out of a CREATE line in a dump file, then go and do another find (ie so you have to of already done a find on CREATE for it to find another one).

Macro

yyP$r;0dwiDROP \ESCn

Note: Replace \ESC with an escape character, you can do this by entering insert mode and then doing Ctrl-V Esc

example

CREATE TABLE ac_table (

would be changed to

DROP TABLE ac_table;
CREATE TABLE ac_table (

This helped me reload a database, without having to drop the whole database.

Exporting Data

You can export select fields out of the database. You can use the select command to export a select part of a database table.  Here is an example that will export the database in a CSV format.

SELECT * 
FROM test_table
WHERE user = 'my_user_id'
INTO OUTFILE '/tmp/result.text'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

By default mysql will export fields in a tab delmimted format.

SELECT * INTO OUTFILE '/tmp/result.text';

Links

http://dev.mysql.com/doc/refman/5.0/en/load-data.html

http://forums.mysql.com/read.php?79,150417,150742#msg-150742

http://forums.mysql.com/read.php?79,11324,13062#msg-13062

Other MySQL Database Info Links

Looks like a good general using mysql page.

http://www.linuxhomenetworking.com/wiki/index.php/Quick_HOWTO_:_Ch34_:_Basic_MySQL_Configuration