Restore MySQL Database Shell Command
mysql -u username -p password databasename < databasefile.sql : Restores a MySQL database from databasefile.sql
Backup MySQL Database Shell Command
mysqldump -u username -p password databasename > databasefile.sql : Backup MySQL database to databasefile.sql
$ ssh server
[user@server ~]$ mysql -u user -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is id
Server version: version
Type 'help;' or '\h' for help. Type '\c' to clear the current input
statement.
mysql> connect cphulkd
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Connection id: id
Current database: cphulkd
mysql> select IP, BRUTETIME from brutes order by BRUTETIME;
Empty set (0.00 sec)
mysql> select IP, LOGINTIME FROM logins order by LOGINTIME;
+---------------------------------+---------------------+
| IP | LOGINTIME |
+---------------------------------+---------------------+
||
mysql> delete from brutes;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from logins;
Query OK, 32 rows affected (0.00 sec)
You can also get the status of it using status command
mysql> status
mysql Ver 14.14 Distrib 5.1.56, for unknown-linux-gnu (x86_64) using readline 5.1
Connection id: 45196
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.1.56-log MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: latin1
Conn. characterset: latin1
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 5 days 19 hours 59 min 11 sec
Threads: 1 Questions: 4376 Slow queries: 1 Opens: 2647 Flush tables: 1 Open tables: 64 Queries per second avg: 0.8
root@localhost [~]# mysqladmin processlist
+-------+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+------+-----------+----+---------+------+-------+------------------+
| 45197 | root | localhost | | Query | 0 | | show processlist |
+-------+------+-----------+----+---------+------+-------+------------------+
mysql -u username -p password databasename < databasefile.sql : Restores a MySQL database from databasefile.sql
Backup MySQL Database Shell Command
mysqldump -u username -p password databasename > databasefile.sql : Backup MySQL database to databasefile.sql
$ ssh server
[user@server ~]$ mysql -u user -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is id
Server version: version
Type 'help;' or '\h' for help. Type '\c' to clear the current input
statement.
mysql> connect cphulkd
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Connection id: id
Current database: cphulkd
mysql> select IP, BRUTETIME from brutes order by BRUTETIME;
Empty set (0.00 sec)
mysql> select IP, LOGINTIME FROM logins order by LOGINTIME;
+---------------------------------+---------------------+
| IP | LOGINTIME |
+---------------------------------+---------------------+
||
mysql> delete from brutes;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from logins;
Query OK, 32 rows affected (0.00 sec)
You can also get the status of it using status command
mysql> status
mysql Ver 14.14 Distrib 5.1.56, for unknown-linux-gnu (x86_64) using readline 5.1
Connection id: 45196
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.1.56-log MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: latin1
Conn. characterset: latin1
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 5 days 19 hours 59 min 11 sec
Threads: 1 Questions: 4376 Slow queries: 1 Opens: 2647 Flush tables: 1 Open tables: 64 Queries per second avg: 0.8
root@localhost [~]# mysqladmin processlist
+-------+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+------+-----------+----+---------+------+-------+------------------+
| 45197 | root | localhost | | Query | 0 | | show processlist |
+-------+------+-----------+----+---------+------+-------+------------------+
