Some MySQL administration commands

• Initial setup after installation to set root password, ...etc

mysql_secure_installation

• Add a serial number to a table

SELECT @n:=0;
UPDATE tbl_proposal_discipline_items SET `order` = @n := @n + 1;

• Change DB Engine:

ALTER TABLE t1 ENGINE=InnoDB;

• Change root password

mysqladmin -u root password 'new-password'

• Change root password - another method for the first time ??

mysql_secure_installation

• Change Password:

SET PASSWORD [FOR user] =
    {
        PASSWORD('some password')
      | OLD_PASSWORD('some password')
      | 'encrypted password'
    }

• Change Password - another method:

UPDATE mysql.user SET Password=PASSWORD('some password')
  WHERE User='someuser' AND Host='localhost';
FLUSH PRIVILEGES;

• Assign permissions:

CREATE USER 'someuser'@'localhost' IDENTIFIED BY 'some password';
GRANT ALL ON somedb.* TO 'someuser'@'localhost';

• Assign permissions - another method:

insert into tables_priv (Host, Db, user, table_name, grantor) VALUES ('localhost', 'somedb', 'someuser', '*', 'root');

• Drop all tables at once:

for T in `echo 'show tables;' | mysql betazedony | tail -n+2`; do echo drop table $T\; ; done | mysql betazedony

• Dump Schema

 mysqldump --no-data somedb -u someuser -p > schema.sql