MySQL

MySQL is by far the most widely used database engine in the Linux world. Here are some tips to help you use this popular tool.

first commands to use

When you've first set up a Linux machine with MySQL on it, these are the first commands to use to get things going:

To set MySQL root password: (note: this is not the same as the machine's root pasword)

mysqladmin -u root password [password you want]

To change the MySQL root password

mysqladmin -u root -p password [new password]

To create a database

mysqladmin -u root -p create [database name]

the MySQL shell

To use MySQL on the command line, open a MySQL client shell like so:

mysql -u [username] -p [database name]

For example, the root user would access a database called 'club' like this:

mysql -u root -p club

and would be prompted for the root password.

basic creation of a table

First, you'd open up a mysql shell, as we did above. Then, to create a table in the database 'club', you would first do:

CREATE TABLE members (
name VARCHAR(50),
address VARCHAR(100));

basic example for adding data

It really isn't efficient to add data to a MySQL table by hand. Scripting languages like Perl and PHP come with built-in functions to do this easier. But if you're in a bind, here's how you do this with the MySQL client shell:

INSERT INTO members(name, address) VALUES ('John Smith', 'Main St.');

basic examples for viewing data

SELECT * FROM members;

This will show all the data from all the fields from the table members.

It might be good idea to restrict your queries a bit. For example, the following only shows the names of the members who live on Main St.:

SELECT name FROM members WHERE address='Main St.';

The following will show only members whose names begin with 'J';

SELECT name FROM members WHERE name like 'J%';

basic example for changing data

As with adding data, it isn't very efficient to change data using the MySQL client shell. But here's an example so you can see how it's done:

UPDATE members SET address = 'Oak St.' WHERE name = 'John Smith';

add a field to a table

If you've created a table but you've inadvertantly left out a field (also known as a column), you can add it after:

ALTER TABLE [table name] ADD COLUMN [field name] TYPE(size);

In our previous examples, if you wanted to add a field for the member's telephone numbers, you could do the following:

ALTER TABLE members ADD COLUMN telephone VARCHAR(15);

remove a field from a table

You can remove a field from a table with this command

ALTER TABLE [table name] DROP COLUMN [field name];

change field name and type

You can change the type of field and its type with this command:

ALTER TABLE [table name] CHANGE COLUMN [column name] [new name] TYPE(VALUE);

backup your data

Second in importance to actually putting the data in the database is backing it up. Lost data means lost time and productivity. We'll use the command line utility to do this (note: this does not use the MySQL client shell). Here's how to back up the database 'club' used in our previous examples:

mysqldump -u [user name] -p --databases club --add-lock > club.sql

The --add-lock options is to make sure that nobody writes to the database while we're backing it up. This can then be restore very easily, also on the regular Linux command line:

mysql -u [user name] -p < club.sql

get data from another type of database

If you've got data stored in some other type of database (like Access) you can create a dump of that into a text file and then upload that data into MySQL. After creating a table for the data, in a MySQL client shell, issue a command like this:

LOAD DATA INFILE '/path/to/file.txt' INTO TABLE [table name]
FIELDS TERMINATED BY ';' ENCLOSED BY '"' LINES TERMINATED BY '\n';

In this example, your individual data is enclosed with quotes (") and the fields are separated by a semi-colon (;). Each entry in the table is ended by a carriage return (\n) in the file.

export data from MySQL

In this example, we'll do the opposite as in the previous example. We'll dump MySQL database data into a text file so it can be used by another type of database:

SELECT * from members INTO OUTFILE '/tmp/members.txt'
fields terminated by ';' enclosed by '"' lines terminated by '\n';

ls in MySQL datetime format

ls will print a directory listing in MySQL's datetime format with this command

ls -lt --full-time *.* | awk '{print $6, $7, $9}' \
| sed 's/.000000000//g'

[ return to main tips page ]