8.5.2 Setting up a simple database using MySQL
Step one. Set the root password for MySQL. Without a root password, anyone from your localhost can log on with root privileges to your MySQL database which is probably not good if you intend to keep there anything usable.
mysqladmin --user=root password 'my_root_password'
If you would like to change a password for a specific user (including root), use the following syntax (in this case the password for the user root is changed from "my_root_password" to "my_NEW_root_password"):
mysqladmin --user=root --password='my_root_password' password 'my_NEW_root_password'
Step two. Create a database to work with. This would be the reason why you actually installed MySQL.
mysqladmin --user=root --password='my_root_password'create my_database
Step three. Log on to MySQL.
mysql --user=root --password='my_root_password'
Once you are logged in, you should get a display that looks something like this,
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 3.23.56
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
The last "mysql>" represents the mysql prompt.
Step four. Create a user that can read, write and create tables in "my_database". To do this I used the GRANT command while logged on to MySQL with root privileges.
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER
-> ON my_database.* TO peter@localhost
-> IDENTIFIED BY 'peters_password';
Note, MySQL only processes the command when ended with a semicolon. Once entered, you should get an output similar to this,
Query OK, 0 rows affected (0.00 sec)
mysql>
To log out of MySQL just type:
mysql> exit
Now you can log on to MySQL with your new user account and start building your new database.
mysql --user=peter --password='peters_password' my_database
Step five. Create a table in my_database. To add a table to your database use the CREATE TABLE command. The syntax for the command is as follows:
CREATE TABLE [table_name] ([column_name] [column_type] [DEFAULT value],...)
My command looked like this:
mysql> CREATE TABLE my_table (person TEXT DEFAULT "", age INTEGER DEFAULT 0) ;
To alter the table once you have created it, you can use the ALTER TABLE command. To add another column to the table my_table with the name "height" of the type REAL and with a DEFAULT value of NULL, I would use the following command:
mysql> ALTER TABLE my_table ADD COLUMN (height REAL DEFAULT NULL) ;
Step six. Populate the table with values. Note, when you enter the values, they must be in the same order as the columns in the table. Use "\N" for a NULL value.
mysql> INSERT INTO my_table VALUES('Bill',28,184) ;
You can also populate the table from an ASCII text tab delimited file. To do this you must pass the option "--local-infile" to MySQL when logging in and then call the LOAD DATA command.
mysql --local-infile --user=peter --password='peters_password' my_database
mysql> LOAD DATA LOCAL INFILE "my_data_filename" INTO TABLE my_table;
* License
