Index
- Create new user
- Backing up
- Locate configuration files
- Max allowed packet error
- MyISAM to InnoDB
- Check a database for errors and repair tables
Create new user | Go to top
The following applies to MySQL versions 4 and 5. MySQL 5 has an additional CREATE USER command, which I will not go into here. See the references.
To create a new user, grant them privileges on a database. For example, here we grant create,select,insert,update and delete privileges on the_database to the_user (allowed to connect only from the_server):
GRANT create,select,insert,update,delete ON the_database.* TO 'the_user'@'the_server' IDENTIFIED BY 'the_password';
To grant permission to do anything to the database:
GRANT ALL PRIVILEGES ON the_database.* TO 'the_user'@'the_server' IDENTIFIED BY 'the_password';
WITH GRANT OPTION can be added to these statements to allow the new user to grant other users permission to do things on the database:
GRANT ALL PRIVILEGES ON the_database.* TO 'the_user'@'the_server' IDENTIFIED BY 'the_password' WITH GRANT OPTION;
References
- 5.8.2. Adding New User Accounts to MySQL from the MySQL 3.23, 4.0, 4.1 Reference Manual.
- 13.5.1.1. CREATE USER Syntax from the MySQL 5.1 Reference Manual
- 13.5.1.3. GRANT Syntax from the MySQL 5.1 Reference Manual
Backing up | Go to top
For MySQL 4 or 5. To write your DB to a file:
mysqldump -u[username] -p [database] > [dump file]
In all examples here, if you don't require a username or password, drop the -u and -p flags.
To restore a backup, create an empty DB with the same name as the one you backed up, then import the backup file:
mysql -u[username] -p [database] < [dump file]
Note: I've been told there is an option to automatically create the DB, but I don't know what it is yet.
Show details of a table's column. This is useful to list the fields without having to perform a query.
mysqlshow [database] [table] [column] -u [username] -p
Locate configuration files | Go to top
You can see what configuration files your version of MySQL is using with the following command:
mysql --help
This will print comprehensive help information, including the location of MySQL's configuration files and the order in which they're read.
References
Max allowed packet error | Go to top
If you get the following error, complaining that MySQL has received too large a data packet:
ERROR 1153 (08S01) at line ??: Got a packet bigger than 'max_allowed_packet' bytes
Increase the value of the max_allowed_packet variable above the default, edit one of MySQL's configuration files (such as /etc/my.cnf, see Locate configuration files). For example, to set the max allowed packet to 256 megs:
[mysqld] max_allowed_packet=256M
References
From the MySQL 5.0 Reference Manual:
MyISAM to InnoDB | Go to top
For instructions on how to convert MyISAM tables to InnoDB, see MyISAM to InnoDB.
Check a database for errors and repair tables | Go to top
To check a database, from the linux command line:
mysqlcheck -u[username] -p [databasename]
To repair a table, first log in to mysql:
mysql -u[username] -p [databasename]
Then run the repair command on a table:
repair table [tablename];