Update Syntax.
mysql> UPDATE table_name SET field1 = value1, field2 = value2 WHERE Clause;
mysql> use mysql;
mysql> UPDATE user SET Host='192.168.1.%' WHERE User = 'user1';
Delete user.
Drop command is better than delete command.
mysql> drop user 'user'@'%';
mysql> delete from user where User = '' and Host = 'localhost';
Set password.
mysql> set password for root@localhost = password('abcde');
Create DB and grant/create new user with password.
create database someDB;
GRANT ALL PRIVILEGES ON someDB.* TO username@"%" IDENTIFIED BY 'password';
Select: Display results one by one.
select * from some_table \G;
Pager option
Shell>mysql --pager='less -S' -uroot -p
Output as CSV
select * from some_table into outfile "./output.csv" fields terminated by ',' enclosed by '"' lines terminated by '\r\n';
Purge binary logs
On slave machine, check the current log position
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: masterdb
Master_User: user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysqld-bin.000037
Read_Master_Log_Pos: 170665205
Relay_Log_File: mysqld-relay-bin.000063
Relay_Log_Pos: 170665343
Relay_Master_Log_File: mysqld-bin.000037
On master machine, check the current status.
mysql> show master status\G;
*************************** 1. row ***************************
File: mysqld-bin.000037
Position: 170680957
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)
mysql> show master logs;
+-------------------+------------+
| Log_name | File_size |
+-------------------+------------+
| mysqld-bin.000035 | 1073743657 |
| mysqld-bin.000036 | 1073745063 |
| mysqld-bin.000037 | 171424254 |
+-------------------+------------+
3 rows in set (0.00 sec)
On master, to delete old binary logs which is prior to mysqld-bin.000037
mysql> purge master logs to mysqld-bin.000037
