Administration
First connection as root from localhost
mysql -u root
Set root password
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpwd')
Now you can connect from localhost using
mysql -u root -p
For security reason, it is better not to allow connection as root from everywhere. Password are stored and communicated encrypted, however SSH is much more robust protocol for encryption.
Add a “normal” user that can connect from localhost as well as from any other host
And provide all privileges on a user database. The user can read, alter, create and delete the database and its content. Do not “GRANT ALL” to the database called “mysql”, or that user will become like root.
CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'password';
CREATE USER 'jeffrey'@'%' IDENTIFIED BY 'password';
CREATE DATABASE cars;
GRANT ALL ON cars.* TO 'jeffrey'@'%';
GRANT ALL ON cars.* TO 'jeffrey'@'localhost';
Jeffrey can now login into mysql (running on x.leeds.ac.uk) from everywhere and from localhost with the command
mysql -h x.leeds.ac.uk -u jeffrey -p
Jeffrey will be prompted with a password to enter.
Note that ‘jeffrey’@’localhost’ and ‘jeffrey’@’%’ could be two different people or the same person but with different permissions.
Create two more users for testing: one that can only access mySQL and another that can read (SELECT) all tables from database precancer.
CREATE USER access_only IDENTIFIED BY 'passw1';
CREATE USER read_only IDENTIFIED BY 'passw2';
GRANT SELECT ON precancer.* TO read_only;
Create a user that can modify entries in a few tables and read from all others
GRANT INSERT, UPDATE, DELETE ON precancer.groups TO user1;
GRANT SELECT ON precancer.* TO user1;
Backups and restore
backups: to be done
to restore a database, using root (I don’t know what operation requires root priviledges):
mysql -u root -p precancer < precancer.sql
where precancer is an already existing database.
Usage
Here some example code for typical usage of the database:
Create/Edit a Table layout
CREATE TABLE myTable (
ID INT NOT NULL AUTO_INCREMENT, name VARCHAR(45),
sex ENUM('m', 'f'), notes MEDIUMTEXT, PRIMARY KEY (ID)
);
I have noticed that some table names are not accepted (they have the same name as a function or internal key). You can then explicitly call it database.myTable.
To change the layout of a table, use ALTER TABLE It is a rather complex command, here I am reporting only a few examples;
change primary keys
ALTER TABLE myTable DROP PRIMARY KEY;
ALTER TABLE myTable ADD PRIMARY KEY (ID);
Insert values into tables
INSERT INTO myTable VALUES ( '', 'Superman', 'M', NULL), ( NULL , 'Cat Woman', 'F', NULL);
Since first value is AUTO_INCREMENT, both an empty and a NULL value will do. This syntax, however, requires that the order and the number of fields are respected. If you don’t know or want to avoid error use the following.
INSERT INTO myTable (name, sex) VALUES ('Batman', 'M');
Alternatively you can also enter key -> value
INSERT INTO myTable SET name='Flash', sex='m';
INSERT SELECT
In case we need to insert data present in another table we can use the following
INSERT INTO MyTable (col1, col2) SELECT colA, colB FROM OtherTable WHERE count > 20;
SELECT ENTRIES NOT SOMEWHERE ELSE
This command select al fileds that DO NOT have a match in the linked table.
SELECT * FROM myTable WHERE myTable.otherTable_ID NOT IN (SELECT ID FROM otherTable)
Edit table Content
We can delete a row (where sex is ‘m’) by entering
DELETE FROM myTable WHERE sex='m';
You can also delete from one or more table according to a JOIN statement
DELETE table1 [, table2] FROM table1 INNER JOIN table2 ON (table1.ID = table2.table1_ID) WHERE table2.name = 'Batman';
Or
DELETE FROM table1 [, table2] USING table1 INNER JOIN table2 ON (table1.ID = table2.table1_ID) WHERE table2.name = 'Batman';
This is useful when we want to delete entry in one table according to info in another table. similarly, you can also do it for UPDATE. Some documentation here and here
Be carefull it is case insensitive! Run a SELECT statement first to check what you are doing
Or we can edit a column (or a series of columns)
UPDATE myTable SET notes='She has nine lives' WHERE name='Cat Woman';
Backups and restore
To perform backup I wrote a little script adapted from these instructions. I create a user called full_backup with the required privileges. To avoid display of the password using ps aux I followed this suggestion
To restore the created backup, you need to create the database first from mySQL
CREATE DATABASE precancer;
and then, from the shell:
mysql -u root -p precancer < precancer-20111014.sql