I’ve always wanted to create my own cheat sheet that lists simple and common examples of MySQL commands that I collected.<\/p>\n
Create a new database<\/strong> Delete a database<\/strong> Create a new user with specific privileges on the database<\/strong> The example above allows the username jack, password H0lyFreak1ng@! access to unfortunate_database but does not have permissions to drop a table or database. Granting specific privileges is particularly useful if you want to provide read only access. Ideally, you would have a user with all privileges and a second user with read-only privileges (grant select on unfortunate_database.*…)<\/p>\n To Backup a MySQL database<\/strong> The mysqldump<\/em> command is done outside of the MySQL server console. \u00a0You don’t necessarily have to be root in order to issue the command nor specify as the user root<\/em> to perform a backup of the database. As long as the user has privileges in the shell and the specified MySQL user has permissions (on the database), you should be fine. Also, the new MySQL dump file will be stored in the present working directory you are in. Which means, if you are on \/home\/jack on some Linux server, unfortunate_database.sql will in that directory. \u00a0The dump file, unfortunate_users.sql is just a text file that includes commands that will restore the data to the database. \u00a0With that, the file doesn’t really need to end in .sql<\/em> extension but is considered good practice.<\/p>\n To create a table<\/strong><\/p>\n To empty out data from a table without wiping out or altering the table structure<\/strong> In case of you run into a scenario where someone sends you a CSV file and you need to load it unto an empty or truncated table<\/p>\n What good is a backup if you can’t restore it? Add a column to an existing MySQL database table<\/strong> I made an error with the above example, I did not intend sometext<\/em> to be a storage for a boolean character. <\/p>\n
\ncreate database unfortunate_database<\/em>;<\/code><\/p>\n
\ndrop database unfortunate_database<\/em>;<\/code><\/p>\n
\ngrant select,update,insert,delete,create on unfortunate_database<\/em>.* to 'jack<\/em>'@'localhost' identified by 'H0lyFreak1ng@!<\/em>';<\/code><\/p>\n
\nmysqldump -u root -proot_password<\/em> unfortunate_database > unfortunate_database.sql<\/em><\/code>
\nor
\nmysqldump -u root -p unfortunate_database<\/em> > unfortunate_database.sql<\/em><\/code><\/p>\n\r\n
CREATE TABLE `unfortunate_users` (\r\n`id` int(8) NOT NULL auto_increment,\r\n`username` varchar(64) NOT NULL,\r\n`password` varchar(64) NOT NULL,\r\n`active` tinyint(1) NOT NULL default '0', \r\nPRIMARY KEY (`id`));<\/code>\r\n<\/pre>\n
\ntruncate table unfortunate_users<\/em><\/code><\/p>\n\r\n
load data local infile 'more_unfortunate_bastards.csv<\/em>' into table unfortunate_<\/em> fields terminated by ';'\r\nenclosed by ''\r\nlines terminated by '\\n'\r\n(column1, column2, column3, column4);<\/code>\r\n<\/pre>\n
\nTo restore a MySQL backup…<\/strong>
\nmysql -u root -p database_name<\/em> < dumpfile.sql<\/em><\/code><\/p>\n
\nALTER TABLE table_name ADD sometext tinyint(1);<\/code><\/p>\n