- 1 Setting the root password;
- 2 Random mysql command examples..
- 3 How can I backup my database?
- 4 How do you get input from a file?
- 5 What are the GRANT options?
- 6 MySQL root password reset
- 7 Specified key was too long; max key length is 1000 bytes
- 8 Building PHP With Support For MySQL
- 9 Where is the Database
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
- Log in to the server as root
- Stop the MySql server by typing:
- Restart safe_mysqld with the skip-grant-tables option by typing:
/usr/bin/safe_mysqld -Sg &
- Start MySql as root by typing:
mysql -u root -p mysql(When prompted for a password, just hit enter)
- 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)
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
# 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