User Tools

Site Tools


mysql

MySQL

Mysql cli connect

mysql -p<pass> -u<user> -h <host> -P <port> <db_name>
mysql -A -p<pass> -u<user> -h <host> -P <port> <db_name>

Install tools in Debian

apt install default-mysql-client
apt install mysql-workbench

Kill a process

show full processlist;
kill <processId>;

(also possible with MySQL Administrator → Server Connections)

Change user password

mysqladmin -u any_user -p'newpwd' password 'oldpwd'
mysqladmin -u root -p'newpwd' password 'oldpwd'

Create a backup table

CREATE TABLE results_bak LIKE results;
INSERT results_bak SELECT * FROM results;

Drop a table

DROP TABLE IF EXISTS results_bak;

Dumps

Dump an entire database

mysqldump -p<pass> -u<user> -h <host> -P <port> <db_name> > dump.sql

Dump the structure of a database

mysqldump -p<pass> -u<user> -h <host> -P <port> --no-data <db_name> > structure.sql

Dump some tables of a database

mysqldump -p<pass> -u<user> -h <host> -P <port> <db_name> \
    table1 \
    table2 \
    table3 \
     > dump.sql

Import the dump in a database

mysql -p<pass> -u<user> -h <host> -P <port> <db_name> < dump.sql

mysql.txt · Last modified: 2017/09/22 17:41 by jdx