Configuring MYSQL

Configuring MYSQL

Requirements

Explanation of requirements.

1. MySQL and MySQL-server installed on a Centos 5.x system
2. DBA access rights to MySQL on a Centos 5.x system

Doing the Work

Basic description of what will be done and what is expected.

1. Database file locations:

In Centos 5.x the location where the raw uncompressed MySQL databases are stored is: /var/lib/mysql

2. Users and DBA’s (Database Admins) create, delete, manipulate:

 
Note: To create, delete or alter a DBA you'll first need to be logged in as the root mysql DBA or a DBA with full access.

Show all users in the mysql database:

mysql> SELECT * FROM mysql.user;

Delete a null user:

mysql> DELETE FROM mysql.user WHERE user = ' ';

Delete all users who are not root:

mysql> DELETE FROM mysql.user WHERE NOT (host="localhost" AND user="root");

Change root user to a less-guessable name (for security):

mysql> UPDATE mysql.user SET user="mydbadmin" WHERE user="root";

Create a new DBA:

mysql> GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' IDENTIFIED BY 'mypass' WITH GRANT OPTION;

Add a new user with specific privileges on a specific database:

mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES ON mydatabase.* TO 'username'@'localhost' IDENTIFIED BY 'mypass';

Add a new DBA with all privileges to a specific database:

mysql> GRANT ALL PRIVILEGES ON mydatabase.* TO 'username'@'localhost' IDENTIFIED BY 'mypass' WITH GRANT OPTION;

Change a user or DBA password:

mysql> UPDATE mysql.user SET password=oldpass("newpass") WHERE User='username';

Delete a user or DBA from the database:

mysql> DELETE FROM mysql.user WHERE user="username";

3. Simple database manipulation:

Show all databases:

mysql> SHOW DATABASES;

Create a database:

mysql> CREATE DATABASE mydatabase;

Use a specific database:

mysql> USE mydatabase;

Delete a database:

mysql> DROP DATABASE mydatabase;

Rename a database from command line (not from within MySQL), there is purposely no space beween -p and mypass:

mysqldump -u dbauser -pmypass databasename > mybackup.sql

mysql -u dbauser -pmypass newdatabasename < mybackup.sql

Show the last 200 queries to your database with the sample table name "queries" and the sample field "query_id":

mysql> SELECT * FROM queries ORDER BY query_id DESC LIMIT 200;

4. Simple table manipulation:

Show all tables within a previously selected database

mysql> SHOW TABLES;

Show data within a specific table in a previously selected database:

mysql> SELECT * FROM tablename;

Rename a table within a specific database:

mysql> RENAME TABLE first TO second;

or

mysql> ALTER TABLE mytable rename as mynewtable;

Drop a table from a specific database:

mysql> DROP TABLE mytable;

5. Simple column and field manipulation:

Show columns within a table:

mysql> DESC mytable;

or

mysql> SHOW COLUMNS FROM mytable;

Update a field within a previously selected database table:

mysql> UPDATE mytable SET mycolumn="newinfo" WHERE mycolumn="oldinfo";

Select data within a specific table in a previously selected database:

mysql> SELECT * FROM mytable WHERE mycolumn='mydata' ORDER BY mycolumn2;

Insert data into a table and its columns within a specific database:

mysql> INSERT INTO mytable VALUES('column1data','column2data','column3data','column4data','column5data','column6data','column7data','column8data','column9data');

Delete data from specific fields within a column:

mysql> DELETE FROM mytable WHERE mycolumn="mydata";

Update data in a specific table, column, field:

mysql> UPDATE mytable SET column1="mydata" WHERE column2="mydata";

6. Simple backup options:

Backup all databases uncompressed from the command line (not from within MySQL):

backup: mysqldump -u root -pmypass --all-databases > alldatabases.sql

restore full: mysql -u username -pmypass < alldatabases.sql (no space in between -p and mypass)

restore single: mysql -u username -pmypass mydb < mydb.sql (no space in between -p and mypass)

Backup all databases compressed from the command line (not from within MySQL):

With bzip2: mysqldump --all-databases | bzip2 -c > databasebackup.sql.bz2 (use bunzip2 to uncompress)

With gzip: mysqldump --all-databases | gzip > databasebackup.sql.gz (use gunzip to uncompress)

Mini script to backup all databases and tables using gzip:

#!/bin/sh

date=`date -I`

mysqldump --all-databases | gzip > /var/backup/dbbackup-$date.sql.gz

Backup a specific database only:

mysqldump -u username -pmypass databasename > backupfile.sql

Backup database structure only:

mysqldump --no-data --databases databasename > structurebackup.sql

Backup a specific database and specific tables within that database only:

mysqldump --add-drop-table -u username -pmypass databasename table_1 table_2 > databasebackup.sql

The syntax for the command to issue is:

mysqldump -u [username] -p[password] [databasename] [table1 table2 ....] > backupfilename.sql

Backing up only the database structure of specific databases, not the actual data:

mysqldump --no-data --databases db1 db2 db3 > structurebackup.sql