• Home...Archives
• Ajax
• Bash
• Java
• JavaScript
• jQuery
• Linux
• MySQL
• Perl
• PHP
• SQLite
• Ubuntu
• Vi
• Windows 7
• All Pages

Adding users with some descriptions on what's going on.

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';

The accounts created by these GRANT statements have the following properties:

• Two of the accounts have a username of monty and a password of some_pass. Both accounts are superuser accounts with full privileges to do anything. One account ('monty'@'localhost') can be used only when connecting from the local host. The other ('monty'@'%') can be used to connect from any other host. Note that it is necessary to have both accounts for monty to be able to connect from anywhere as monty. Without the localhost account, the anonymous-user account for localhost that is created by mysql_install_db would take precedence when monty connects from the local host. As a result, monty would be treated as an anonymous user. The reason for this is that the anonymous-user account has a more specific Host column value than the 'monty'@'%' account and thus comes earlier in the user table sort order.
• One account has a username of admin and no password. This account can be used only by connecting from the local host. It is granted the RELOAD and PROCESS administrative privileges. These privileges allow the admin user to execute the mysqladmin reload, mysqladmin refresh, and mysqladmin flush-xxx commands, as well as mysqladmin processlist. No privileges are granted for accessing any databases. You could add such privileges later by issuing additional GRANT statements.
• One account has a username of dummy and no password. This account can be used only by connecting from the local host. No privileges are granted. The USAGE privilege in the GRANT statement enables you to create an account without giving it any privileges. It has the effect of setting all the global privileges to 'N'. It is assumed that you will grant specific privileges to the account later.
• The statements that create accounts with no password will fail if the NO_AUTO_CREATE_USER SQL mode is enabled. To deal with this, use an IDENTIFIED BY clause that specifies a non-empty password.

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

User passwords

You can do an

mysql> update user set password=password('somepassword') where Host='hostname' and User='username';
mysql> flush privileges;

or for the old password system

mysql> update user set password=old_password('somepassword') where Host='hostname' and User='username';
mysql> flush privileges;

Variables and math.

You can set variables in mysql and perform math. Some examples are below.

mysql> set @x=1;
mysql> set @y=5;
mysql> select @x + @y;

mysql> select sum(field) from table;
     Will return the sum of a column from a table.

Selecting

mysql> select * from table;
     Returns every row from the table.

mysql> select * from table where conditions;
     Returns every row from the table based on the conditions.

mysql> select field,field,field from table where conditions;
     Returns the contents you requested in a table format.
     You can also use \G instead of ; which will list the 
     contents in a more readable format.

mysql> select if(condition,true,false);
     Returns true or false is returned based on the condition. e.g. 5>7

Inserting

mysql> insert into table values (value,'value','value',value,'',value);

or

mysql> insert into table (field,field,field) values ('','','');

The second insert will insert a timestamp if you have a timestamp field.

Text fields must be surrounded by apostrophes (') or quotation marks (").

Fixing Errors

If you get an error like,

do

mysql> repair table a_table use_frm;

Rename a table

mysql> rename <table> <table_name_from> to <table_name_to>;

mysqldump compatibility

Say you are using MySQL 5 and you want to create a dump for MySQL 4, you can use the command,

# mysqldump -u username -p --compatible=mysql40 database > database.sql

The complete usage for MySQL 5 mysqldump can be found at http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html ».

[Click to add or edit comments])

Please prepend comments below including a date

2012-02-10