MySQL

From The System Administrator Zone

Setting the root password;

shell> mysql -u root mysql
mysql> SET PASSWORD FOR root@localhost=PASSWORD('new_password');
mysql> FLUSH PRIVILEGES;

Random mysql command examples..

shell> mysql -h hostname -u username -p
mysql> SHOW DATABASES;
mysql> DROP DATABASE test;
mysql> CREATE DATABASE jokes;
mysql> USE jokes;
mysql> CREATE TABLE table_name (
-> column_1_name column_1_type column_1_details,
-> column_2_name column_2_type column_2_details,
-> ...
-> );
mysql> SHOW TABLES;
mysql> DESCRIBE Jokes;
mysql> DROP TABLE tableName;
mysql> CHECK TABLE tableName;

How can I backup my database?

You can dump all databases to an SQL file:

mysqldump -Aal -u username -p password > ...backup/21feb2003.sql

How do you get input from a file?

mysql -u USERNAME -p DATABASE < source.sql

What are the GRANT options?

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;
mysql> GRANT ALL PRIVILEGES ON db_base.* TO db_user @'%' IDENTIFIED BY 'db_passwd'; 

MySQL root password reset

  1. Log in to the server as root
  2. Stop the MySql server by typing: /etc/rc.d/init.d/mysqld stop
  3. Restart safe_mysqld with the skip-grant-tables option by typing:/usr/bin/safe_mysqld -Sg &
  4. Start MySql as root by typing: mysql -u root -p mysql (When prompted for a password, just hit enter)
  5. Set a new password for root by typing:
update user set password=password("new_pass") where user='root';
(Replace new_pass with your new, desired password)
flush privileges;
quit;

Specified key was too long; max key length is 1000 bytes

Moving databases from a 4.x server to a 5.x server may trigger this error. The problem is that every utf-8 character is considered 3 bytes long within an index, but indexes in MySQL are limited to 1000 bytes long (767 bytes for InnoDB tables). The limits are measured in bytes, whereas the length of a column is interpreted as number of characters.


I was bit by this when moving a database from one ISP to another.

In the example here, we reduce the size from the "standard" 255 bytes to 165.

mysql> CREATE TABLE t1 (
  ->   c1 varchar(255) NOT NULL default '',
  ->   c2 varchar(255) NOT NULL default ''
  ->   ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> ALTER TABLE t1
  ->   ADD INDEX idx1 ( c1 , c2 );
ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes

mysql> ALTER TABLE t1
  ->   ADD INDEX idx1 ( c1(165) , c2(165) );
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

This came from the Gentoo Upgrade guide to MySQL 4.* or 5.0.*.

Building PHP With Support For MySQL

./configure --with-mysql=/path/to/mysql/directory
# make test

Build complete.
Don't forget to run 'make test'.

dyld: Library not loaded: /usr/local/mysql/lib/mysql/libmysqlclient.15.dylib
  Referenced from: /usr/local/src/php-5.2.3/sapi/cli/php
  Reason: image not found
cd /usr/local/mysql/lib 
mkdir mysql
cp libmysqlclient.15.dylib mysql/libmysqlclient.15.dylib

Where is the Database

The database is normally controlled by the datadir statement in /etc/my.cnf.

OS-X: /usr/local/mysql/data
[mysql]
datadir  =  /s1/Disk/mysql/data