MySQL Databases: Difference between revisions
Jump to navigation
Jump to search
No edit summary |
No edit summary |
||
| (One intermediate revision by the same user not shown) | |||
| Line 7: | Line 7: | ||
* Create new UTF-8 database called <code>mydatabase</code> (at time of writing, 20190214, these settings seem to be the best practice for UTF-8 settings, YMMV, update if needed) | * Create new UTF-8 database called <code>mydatabase</code> (at time of writing, 20190214, these settings seem to be the best practice for UTF-8 settings, YMMV, update if needed) | ||
CREATE DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; | CREATE DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; | ||
* Change the encoding of <code>mydatabase</code> (because some software may not support latest best practices, etc) | |||
ALTER DATABASE mydatabase CHARACTER SET = utf8 COLLATE = utf8_general_ci; | |||
* Give full control of <code>mydatabase</code> to existing <code>newuser</code> | * Give full control of <code>mydatabase</code> to existing <code>newuser</code> | ||
GRANT ALL PRIVILEGES ON mydatabase.* TO newuser@localhost; | GRANT ALL PRIVILEGES ON mydatabase.* TO newuser@localhost; | ||
FLUSH PRIVILEGES; | |||
[[Category:Database]] | [[Category:Database]] | ||
Latest revision as of 14:24, 24 February 2019
Some MySQL handy oneliners:
- List all users from running instance:
SELECT User, Host, Password FROM mysql.user;
- Create new user called
newuser
CREATE USER newuser@localhost IDENTIFIED BY 'superstrongpassword';
- Create new UTF-8 database called
mydatabase(at time of writing, 20190214, these settings seem to be the best practice for UTF-8 settings, YMMV, update if needed)
CREATE DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
- Change the encoding of
mydatabase(because some software may not support latest best practices, etc)
ALTER DATABASE mydatabase CHARACTER SET = utf8 COLLATE = utf8_general_ci;
- Give full control of
mydatabaseto existingnewuser
GRANT ALL PRIVILEGES ON mydatabase.* TO newuser@localhost; FLUSH PRIVILEGES;