Index
- User admin
- Backing up
- Locate configuration files
- Max allowed packet error
- MyISAM to InnoDB
- Check a database for errors and repair tables
- Multiple access details in my.cnf
User admin
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 using the GRANT command.
Grant
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;
Revoke
To remove grants you revoke.
REVOKE create,select,insert,update,delete ON the_database.* FROM 'the_user'@'the_server'
REVOKE ALL PRIVILEGES ON the_database.* FROM 'the_user'@'the_server;
There’s a bug in MySQL 5 which means that you can’t use the REVOKE command in the same was as you’d use GRANT when issuing GRANT OPTION. See Bug #45684 GRANT ALL and REVOKE ALL doesn’t work with GRANT OPTION.
This won’t work:
REVOKE ALL PRIVILEGES, GRANT OPTION ON the_database.* FROM 'the_user'@'the_server';
Nor will this:
REVOKE ALL PRIVILEGES, GRANT OPTION ON the_database.* FROM 'the_user'@'the_server' WITH GRANT OPTION;
Instead you have to revoke all and grant separately:
REVOKE ALL PRIVILEGES ON the_database.* FROM 'the_user'@'the_server';
REVOKE GRANT OPTION ON the_database.* FROM 'the_user'@'the_server';
To remove permission to do anything on anything, use the following:
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'the_user'@'the_server';
REVOKE removes privileges but does not delete the user (they will still exist in the mysql.user table). To delete a user account use DROP USER.
References
- 5.8.2. Adding New User Accounts to MySQL 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.
- 13.7.1.5. REVOKE Syntax from the MySQL 5.1 Reference Manual.
Backing up
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
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
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
For instructions on how to convert MyISAM tables to InnoDB, see Converting MySQL tables from MyISAM to InnoDB.
Check a database for errors and repair tables
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];
Multiple access details in my.cnf
You can specify default access details in ~/.my.cnf (in Linux):
[client]
user=foo
password=bar
If you use mysql or mysqladmin command it will use these credentials.
You can specify more credentials alongside the default:
[client]
user=foo
password=bar
[clientyay]
user=goo
password=gar
Then call the mysql commands as follows:
mysql --defaults-group-suffix=yay etc
Reference: Is it possible to have passwords configured per database or per host in .my.cnf
