Mysql commandes diverses

Commandes divers

describe nomdunetable : info sur la structure d'une table
show full processlist : info sur les requetes en cours
show variables : donne les variables du serveur mysql
show variables like '%char%': donne les variables incluant *char*
\G à la fin d'une requete sort le résultat en ligne et non en colonne
\P more : définit le pager sur more, ce qui permet d'afficher page par page
show grants for user@host : info sur les droits de ce user

Exemple de création de tables:
CREATE TABLE `pop3_imap_proxy` (`user` varchar(128) NOT NULL,`servername` varchar(255) NOT NULL,`port` varchar(8) default NULL,PRIMARY KEY  (`user`),
KEY `idxtblPerdition_user` (`user`)) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Pb avec mysql 4.1 et client 3.23

mysql > SET PASSWORD FOR 'postfix'@'localhost' = OLD_PASSWORD('reixaetu');
mysql > flush privileges;

ou

# mysqladmin -uroot! -p -hip_du_serveur! old-password nouveaupassword!

ou

dans le my.cnf en mettant :
set_variable = old_passwords=1 dans la partie [mysqld]

Création d'un user

Création du user wikiuser et du mot de passe crypté (via identified) et des droits sur la base wikidb :grant all on wikidb.* to 'wikiuser'@'%' identified by "";

INSERT INTO `user` ( `Host` , `User` , `Password` , `Select_priv` , `Insert_priv` , `Update_priv` , `Delete_priv` , `Create_priv` , `Drop_priv` , `Reload_priv` , `Shutdown_priv` , `Process_priv` , `File_priv` , `Grant_priv` , `References_priv` , `Index_priv` , `Alter_priv` ) VALUES ('localhost', 'u_cpop_ng', 'xxxxpasswordxx', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N');

Commandes mysql en bash

mysql -u postfix -e "INSERT into mailbox (username,password,name,maildir,domain) values ('$MBOX', '$PASSWD', '$NAME', '$MBOX/', '$DOMAIN');" -pmotdepasse postfix

Reset du password root

1. Take down the mysqld server by sending a kill (not kill -9) to the mysqld server. The pid is stored in a `.pid' file, which is normally in the MySQL database directory:

shell> kill `cat /mysql-data-directory/hostname.pid`

You must be either the Unix root user or the same user mysqld runs as to do this.
2. Restart mysqld with the --skip-grant-tables option.
3. Set a new password with the mysqladmin password command:

shell> mysqladmin -u root password 'mynewpassword'

4. Now you can either stop mysqld and restart it normally, or just load the privilege tables with:

shell> mysqladmin -h hostname flush-privileges

5. After this, you should be able to connect using the new password.

Alternatively, you can set the new password using the mysql client:

1. Take down and restart mysqld with the --skip-grant-tables option as described above.
2. Connect to the mysqld server with:

shell> mysql -u root mysql

3. Issue the following commands in the mysql client:

mysql> UPDATE user SET Password=PASSWORD('mynewpassword')
-> WHERE User='root';
mysql> FLUSH PRIVILEGES;

4. After this, you should be able to connect using the new password.
5. You can now stop mysqld and restart it normally.