legacy-wiki
Mysql
Recovered from the older tannerjc.net wiki snapshot dated January 23, 2016.
Create a new user and database for a web app
[root@server sites]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 117675
Server version: 5.0.45 Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql create user 'drupal'@'localhost' identified by 'some_pass';
Query OK, 0 rows affected (0.04 sec)
mysql create database drupal;
Query OK, 1 row affected (0.00 sec)
mysql grant all privileges on drupal.* to 'druapl'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql quit
Bye
change password
shell SET PASSWORD FOR 'jeffrey'@'localhost' = PASSWORD('mypass');
http://dev.mysql.com/doc/refman/5.1/en/assigning-passwords.html
viewing data
mysql show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| devtrianglebeats |
| gallery2_john |
| john_wp |
| mysql |
| trianglebeats |
+--------------------+
6 rows in set (0.00 sec)
mysql use trianglebeats;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql show tables;
+-----------------------------+
| Tables_in_trianglebeats |
+-----------------------------+
| access |
| actions |
| actions_aid |
| authmap |
| batch |
| blocks |
| blocks_roles |
| boxes |
| cache |
| cache_block |
| cache_content |
| cache_filter |
| cache_form |
| cache_menu |
| cache_mollom |
| cache_page |
| cache_update |
| cache_views |
| cache_views_data |
| comments |
| content_node_field |
| content_node_field_instance |
| content_type_event |
| date_format_locale |
| date_format_types |
| date_formats |
| files |
| filter_formats |
| filters |
| flood |
| forum |
| history |
| listhandler |
| listhandler_prefix |
| mailhandler |
| mailman_lists |
| mailman_users |
| menu_custom |
| menu_links |
| menu_router |
| mollom |
| mollom_form |
| node |
| node_access |
| node_comment_statistics |
| node_counter |
| node_revisions |
| node_type |
| nodewords |
| nodewords_custom |
| permission |
| role |
| search_dataset |
| search_index |
| search_node_links |
| search_total |
| semaphore |
| sessions |
| system |
| term_data |
| term_hierarchy |
| term_node |
| term_relation |
| term_synonym |
| upload |
| url_alias |
| users |
| users_roles |
| variable |
| views_display |
| views_object_cache |
| views_view |
| vocabulary |
| vocabulary_node_types |
| watchdog |
+-----------------------------+
75 rows in set (0.00 sec)
mysql describe users;
+------------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+------------------+------+-----+---------+----------------+
| uid | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(60) | NO | UNI | | |
| pass | varchar(32) | NO | | | |
| mail | varchar(64) | YES | MUL | | |
| mode | tinyint(4) | NO | | 0 | |
| sort | tinyint(4) | YES | | 0 | |
| threshold | tinyint(4) | YES | | 0 | |
| theme | varchar(255) | NO | | | |
| signature | varchar(255) | NO | | | |
| signature_format | smallint(6) | NO | | 0 | |
| created | int(11) | NO | MUL | 0 | |
| access | int(11) | NO | MUL | 0 | |
| login | int(11) | NO | | 0 | |
| status | tinyint(4) | NO | | 0 | |
| timezone | varchar(8) | YES | | NULL | |
| language | varchar(12) | NO | | | |
| picture | varchar(255) | NO | | | |
| init | varchar(64) | YES | | | |
| data | longtext | YES | | NULL | |
| timezone_name | varchar(50) | NO | | | |
+------------------+------------------+------+-----+---------+----------------+
20 rows in set (0.01 sec)
mysql select name from users;
+-------------------------+
| name |
+-------------------------+
| |
| afreeman |
| Alan_D |
| andrius |
| awcrosby |
| a_w |
| BrianSD |
| Cuban_Rave.Bunny |
| Daxter75 |
| DJ Nugz |
| dj40oz |
| djfm |
| Djpsylo |
| Dr. Brutus Powers |
| ewhirsh |
| Feinberg |
| fiddle |
| jbredhat |
| JBstrikesagain |
| jbyahoo |
| John Brier |
| Mail Delivery Subsystem |
| McSwizzle |
| pcon |
| purrfekt |
| rarousse |
| Sean Hennessey |
| SHATTA |
| snack |
| Trixie Peach |
| uzoma |
| W08 |
+-------------------------+
32 rows in set (0.00 sec)
mysql
Backup and restore
shell mysqldump db_name backup-file.sql
shell mysql db_name backup-file.sql
http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html
File Recovery
- if only files were deleted, use mysqldump to export all data from the process running in RAM
- http://ostatic.com/blog/so-you-just-deleted-your-production-database-what-now