AlanStudio Linux Tutorial

[root@alanstudio]# mysql -uroot -p

[root@alanstudio]# mysqladmin -uroot password 123456
[root@alanstudio]# mysqladmin -uroot -p password 123456

set or change mysql user 'root' password
First time set password, without -p

[root@alanstudio]# mysqldump -uroot -p databasename > databasename.sql
mysqldump -uroot -p --all-databases > AllDatabase.sql

Dump database to backup file .sql

[root@alanstudio]# mysql -uroot -p databasename < databasename.sql

Restore database to backup file .sql

[root@alanstudio]# cp -fr /var/lib/mysql /home/backup

Backup whole mysql and database directory

[root@alanstudio]# mysql -D databasename -uroot -p123456 -e 'show tables;'
[root@alanstudio]# mysql -D databasename -uroot -p123456 -se 'show tables;'

Direct execute the mysql command in linux shell (-se show without layout)

mysql> show databases;

show all databases in mysql

mysql> use databasename;

use(logon to) specific database in mysql

mysql> drop database databasename;

delete database in mysql

mysql> show tables;

show all tables in one of database in mysql

mysql> select * from tablename;

show all record in specific table in mysql

mysql> source xxx.sql;

use the xxx.sql source script

mysql> show columns from tablename;

show columns in specific table in mysql

mysql> desc tablename;

show the format/description of table in mysql

mysql> INSERT INTO tablename VALUES('field1','field2',field3');

INsert new row record into table in mysql

mysql> update tablename set city='hk' where id='3' and city='tw';

update record in specific table in mysql

mysql> update tablename set password=encrypt('123456') where name='alan;

update encrypt password record in specific table in mysql

mysql> delete from tablename where name='alan' and city='hk' and id='5';

delete one row record in specific table in mysql

mysql> use mysql; mysql> select host,user,password from user;

use default mysql database and query the information of table 'user' in mysql

mysql> GRANT ALL PRIVILEGES ON *.* TO root@localhost IDENTIFIED by 'password';
mysql> GRANT ALL PRIVILEGES ON db.table TO root@localhost IDENTIFIED by 'password';

grant the all privileges for user 'root' in mysql

mysql> GRANT ALL PRIVILEGES ON *.* TO root@'%' IDENTIFIED by 'password';

grant the all privileges in all databases for user 'root' and allow other server/pc to access the server's mysql

mysql> GRANT ALL PRIVILEGES ON db.* TO user@'%' IDENTIFIED by 'password';
mysql> GRANT ALL PRIVILEGES ON db.* TO user@localhost IDENTIFIED by 'password';

grant the all privileges in db database for user 'user' and allow other server/pc to access the server's mysql

mysqlbinlog -v --base64-output=decode-rows --read-from-remote-server -uroot -pPassWord -h192.168.1.100 mysql-bin.001234 > mysql-bin.001234.log

To convert base64 binary log to plain text