Create a user:
GRANT ALL PRIVILEGES ON *.* TO user@"%" IDENTIFIED BY 'pass' WITH GRANT OPTION;
Create a password, change password:
set password for user = password('newpassword')
Create a string column namepc with a length of 20 characters in the clients table
ALTER TABLE `clients` ADD `namepc` VARCHAR( 20 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'path to the computer';
Modify table row data
UPDATE `basename`.`taclename` SET `mobtel` = '050888888',
`email` = 'name@name.com.ua', `icq` = '55555555' WHERE `name` ='username' LIMIT 1 ;
Clear the table:
TRUNCATE `mw_words_positions`
Create a backup
mysqldump -u USER -pPASSWORD DATABASE > /path/to/file/dump.sql
Create the database structure without data
mysqldump --no-data - u USER -pPASSWORD DATABASE > /path/to/file/schema.sql
If you need to dump only one or several tables
mysqldump -u USER -pPASSWORD DATABASE TABLE1 TABLE2 TABLE3 > /path/to/file/dump_table.sql
Create a backup and immediately archive it
mysqldump -u USER -pPASSWORD DATABASE | gzip > /path/to/outputfile.sql.gz
Create a backup with a specified date
mysqldump -u USER -pPASSWORD DATABASE | gzip > `date +/path/to/outputfile.sql.%Y%m%d.%H%M%S.gz`
Load the backup into the database
mysql -u USER -pPASSWORD DATABASE < /path/to/dump.sql
Load the backup archive into the database
gunzip < /path/to/outputfile.sql.gz | mysql -u USER -pPASSWORD DATABASE
or like this
zcat /path/to/outputfile.sql.gz | mysql -u USER -pPASSWORD DATABASE
Create a new database
mysqladmin -u USER -pPASSWORD create NEWDATABASE
It is convenient to use a backup with additional options -Q -c -e, i.e.
mysqldump -Q -c -e -u USER -pPASSWORD DATABASE > /path/to/file/dump.sql, where:
- -Q wraps names in backticks
- -c creates a full insert, including column names
- -e creates an extended insert. The resulting file is smaller and created slightly faster
To view the list of databases, you can use the command:
mysqlshow -u USER -pPASSWORD
You can also view the list of tables in the database:
mysqlshow -u USER -pPASSWORD DATABASE
For InnoDB tables, you need to add --single-transaction, which guarantees the integrity of the backup data.