Adding a new user to MySQL and other basic mysql commands

Crontab entry to backup all mysql databases to a file every midnight:
0 0 * * * mysqldump -u mjeltsch -h localhost --all-databases | gzip -9 > /home/mjeltsch/Documents/mysqldump.gz > /dev/null

Granting privileges to users connecting from localhost:
GRANT ALL PRIVILEGES ON *.* TO 'michael'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;

Granting privileges to users connecting from everywhere:
GRANT ALL PRIVILEGES ON *.* TO 'michael'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;

Show all available databases:
SHOW DATABASES;

Show all entries of the table "users":
SELECT * FROM users;

Load database "phpgedview":
USE phpgedview;

Create a new database named "phpgedview":
CREATE DATABASE phpgedview;

Delete the database named "phpgedview":
DROP DATABASE phpgedview;

Start mysql client as user michael and user database "phpgedview":
mysql --user=michael -p phpgedview

Delete the user "test":
mysql> use mysql;
mysql> delete from user where user='test';
mysql> FLUSH PRIVILEGES;

Changing the password for the user phpgedview being user root (this works also for changing the password for root):
/usr/bin/mysql -u root -p
SET PASSWORD FOR phpgedview@"localhost" = PASSWORD('NewPassWord');

After installing the Suse rpm for mysql, execute the following commands:

sudo /usr/bin/mysql_install_db

This creates the default databases & permissions. Apparently the same can be done by:

sudo rcmysql start

Using SuSE you should use the Yast runlevel editor to start the mysql server automatically at boot time. To start the mysql server manually:

sudo /usr/bin/mysqld_safe --user=mysql &

REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! This is done with:

/usr/bin/mysqladmin -u root password 'new-password'

If you access the mysql server from another machine you need to specify the hostname:

/usr/bin/mysqladmin -u root -h hostname password 'new-password'

Give all privileges to root and user:

mcblpc2:/home/user /usr/bin/mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 to server version: 4.0.15 Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> GRANT ALL PRIVILEGES ON *.* TO user@localhost IDENTIFIED BY 'Password' WITH GRANT OPTION; Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO user@'%' IDENTIFIED BY 'Password' WITH GRANT OPTION; Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO root@localhost IDENTIFIED BY 'Password' WITH GRANT OPTION; Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO root@'%' IDENTIFIED BY 'Password' WITH GRANT OPTION; Query OK, 0 rows affected (0.00 sec)
mysql> quit Bye

Check the mysql version:

/usr/bin/mysqladmin -u root -p version" is necessary

Check the mysql variables:

/usr/bin/mysqladmin -u root -p variables

Shut down the mysql server:

/usr/bin/mysqladmin -u root -p shutdown

Check whether the server can be started:

sudo /usr/bin/mysqld_safe --log &

Show all databases:

/usr/bin/mysqlshow -u root -p

Show the tables of database "mysql":

/usr/bin mysqlshow -u root -p mysql

Show the columns of the table named 'tablename':

show columns from tablename;

Remove whitespaces from the column named 'columnname' in the table named 'tablename':

update `tablename` set `columnname` = trim(' ' from `columnname`);

Remove trailing line breaks from the column named 'columnname' in the table named 'tablename' (you might need to execute this repeatedly for some strange reason to remove all carriage returns and line feeds):

update `tablename` set `columnname` = trim(trailing '\n' from `columnname`);
update `tablename` set `columnname` = trim(trailing '\r' from `columnname`);