Wednesday, May 11, 2016

MySQL on Ubuntu

To install MySQL on Ubuntu:
sudo apt install mysql-server
As part of the install, it will ask you to set a password for the root user. If you later want to change that password, you can do (note that you should type password verbatim, not the new password you intend to use)
mysqladmin -u root -p password
This will ask you for current password, and then ask you to enter a new password, and confirm entry. If you did not pick a password during installation, skip the -p flag in the above command.

To start mysql,
mysql -u root -p
This will ask you for the root password, and then start mysql.

To quit out of mysql, use:
To show databases, at the mysql prompt, use
show databases;
To switch to using a specified database:
use [database-name];
To show tables within a database, first switch to that database using the above, and then:
show tables;
So at this point, the next logical step would be to create a user other than the superuser. But then, we also want that user to be able to play with some database.
So first let's create a test database named ubuntu_test:
create database ubuntu_test;
Now let's create a new user named ubuntu, and grant them all privileges on the ubuntu_test database:
create user 'ubuntu' identified by 'password';
That created a user with username "ubuntu" and user password "password".
grant all privileges on ubuntu_test.* to 'ubuntu';
Now, quit out of mysql and restart mysql as your ubuntu user:
mysql -u ubuntu -p
If you do "show databases;", you can see that the ubuntu user has access to only a limited set of databases, including the ubuntu_test database we created. At this point, we can create tables in the database etc using regular SQL commands, and be on our merry way.
use ubuntu_test;
create table [tablename] [details...];
While I do not wish to bore you with elementary SQL commands, there is one command I find rather useful - how to inspect the schema of a pre-existing table:
show create table [tablename];

No comments: