Intro to MySQL R R
Return to Top
This intro to MySQL is meant to help beginners get started. The commands I will present should help you find your way around the database, and also point you in the right direction for more info. If you don't know what myqsql does, read the part in blue below. Otherwise, click here to skip the intro.

MySQL is a database. A database has tables with rows and columns for storing information. For example, a company has employees. The info about such employees may be stored the old fashioned way by printing them on papers. The papers are put in folders and the folders in turn put away in locked drawers. If the company needs info about an employee, the drawer is opened, and the employees folder is pulled out. A good example is medical records in a hospital. Mysql is like a drawer where info is stored in electronic format. Whenever stored info is needed, you issue commands (queries), and mysql displays the info you need. My aim in this tutorial is to acquint you with the rudimentary commands you need to begin using mysql. Whether you're a multi-national company, or just a 12-yr old, you can find a use for mysql. Best of all, it's free. BTW, MySQL is one of many types of Structured Query Languages.

Return to Top
There are alternatives to most commands, but we're going to look at just the basics. You should then consult the MySQL manual for detailed options and examples.

Return to Top

Overview

All mysql commands end with a semicolon ;.
Eg. show databases; If you forget to enter the semicolon, mysql will not do anything until you do.

Let me briefly explain how mysql info is structured.

Return to Top

Downloading MySQL

Return to Top

Installation

The installation process may be slightly different depending on the version you use. The version of mysql I installed at the time of writing is 3.23.46a.

Return to Top

Configuration

The configuration process is by far the most important. Pay close attention here. Any missed step may end up in grief and long frustration for you.

	[WinMySQLAdmin]
	Server=C:/MYSQL/bin/mysqld.exe
	user=your_user_name
	password=your_user_password

	[mysqld]
	basedir=C:/MYSQL
	#bind-address=XXX.XXX.XXX.XXX
	datadir=C:/MYSQL/data
	language=C:/MYSQL/share/english
	#slow query log#=
	#tmpdir#=
	port=3306
	#set-variable=key_buffer=16M
	skip-innodb

Change the following:

Return to Top

STARTING MYSQL

Under win9x, mysql is installed in c:\mysql by default. You however, get a chance to change the installation directory. Once mysql is installed, you can run it as follows:

Not too comfortable with the dos command line? Well, you can do most things with MySQL's GUI, called WinMySQLAdmin.

Return to Top

TESTING MYSQL

To know if mysql is running, open a dos window and change to c:\mysql\bin, then issue one of the commands below: If you get errors with the commands, then you have some problem, which you can try to solve using my earlier suggestions.

Return to Top

SETTING A ROOT PASSWORD

Your initial installation of mysql on Win9x is wide open for anyone to connect without a password. Of course that is not a good thing. You have to make it secure by setting a password. But how do you do this? Well here we go...

Pay very close attention to the steps below. I use these same steps every time I do a new install, and it worked each time.

Return to Top

CONNECTING TO MYSQL

Once mysql is running, you have to connect to it before you can begin using it. The general format is

c:\mysql\bin> mysql -h host_name -u user_name -p

In the above command just replace host_name with the name of your host, eg. localhost, or cnn.com, whatever your hostname is. Localhost will work if you're running mysql on the same machine on which mysql is installed.

Replace user_name with the appropriate name, eg. root. Press Enter and enter the password when prompted. You should see something like Welcome to the MySQL monitor. Commands...

Return to Top

DISCONNECTING FROM MYSQL

When you're done using mysql, you can disconnect from it using QUIT.

mysql> quit

Note that you've only just disconnected from mysql as a user. mysqld is still running in the background. If you want to shutdown the actual server, you must use mysqladmin. See later.

Return to Top

CREATING NEW USERS

To see current users, while logged in to the server as a user, issue the command

mysql> SELECT Host FROM mysql.user;

mysql is the name of a database, and user is a table within mysql where user accounts are stored. You can add new users by using GRANT. You must be connected as root to be able to add users.

To connect as root,

Return to Top

CONNECTING TO MYSQL REMOTELY

So how do you connect from a remote machine to a mysql server with IP address 128.206.100.15? So there you have it.

Return to Top

How to Reset Root Password

What do you do if you forget the root password? You will need to change it as follows: Return to Top

SHUTDOWN

Shutdown using
C:\mysql\bin\>mysqladmin --user=root --password=your_password shutdown. Replace your_password with the root password.

Return to Top

CREATING A DATABASE

mysql> CREATE DATABASE db_name;

Creating a database is no big deal. Just use the command above. Change db_name to any name you want.

Return to Top

USING A DATABASE

To begin using a particular database, issue the command
mysql> USE db_name
Change db_name to the name of the database.
To see the databases you currently have,
mysql> SHOW DATABASES;

Return to Top

CREATING A TABLE

When creating a table, . Here is an example.

mysql> CREATE TABLE test (
last_name VARCHAR(30) NOT NULL,
first_name VARCHAR(30) NOT NULL,
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id));

Our table will have the name test, and will have columns called last_name and first_name - these can be any name you want. VARCHAR() is used to tell mysql what type of column we want. VARCHAR(30) means that our column can have from 0 to 30 characters. Other column types are: BLOB, TEXT, CHAR, ENUM, DATE, DATETIME, TIMESTAMP, TIME, YEAR, SET. There's a lot to learn about the use of these column types, which you can read up in the manual.

When you create a table, each column will have a DEFAULT value. Unless you specifically tell MySQL the default value, each column defaults to NULL.

If you want to specify default values, just do the following

mysql> CREATE TABLE test (
last_name VARCHAR(30) DEFAULT 'Doe' NOT NULL,
first_name VARCHAR(30) DEFAULT 'John' NOT NULL,
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id));

Return to Top

ADDING DATA TO YOUR TABLE

You can add data to your table two ways: manually, or from a text file.

Manually
mysql> INSERT INTO tbl_name (col1, col2) VALUES(23, col1*2);

col1, col2 are the names of your columns. 23, col1*2 means insert 23 into col1, and multiply that by 2 in col2. Just like in a spreadsheet, when the value in col1 changes, col2 is automatically updated.

LOADING DATA INTO A TABLE from a text file

You can add data to a table by doing it one row at a time as we did above. This involves a lot of typing if you have many columns, and also prone to typing errors. An easier way is to put all your data into a text file, then tell mysql to load all the data at once.

But wait a minute, how does mysql know which info goes in which column if all the data is put in a single text file?
Well, what you do is

  1. separate each data with a tab. When mysql encounters a tab, it knows that the next data goes into a new column,
  2. separate each record with a return. When mysql encounters a new line, it knows that the next data goes into a new row.
Note: when entering your data into the text file, enter one record per line. So if you have 100,00 records for instance, you will enter them into a text file one record per line. You only press Enter at the end of each record.

Once all records have been entered, save the file into c:\mysql\data as file_name.txt. file_name can be any valid name you want. Now to populate your table with the data, issue the command

mysql> LOAD DATA INFILE "./file_name.txt" INTO TABLE db_name.table_name;

Change db_name to the name of your database, and table_name to the name of the table you want to add data to.

Easy huh!

Return to Top

UPDATING DATA

To change one of the values in your table, you use UPDATE.

mysql> UPDATE table_name SET age=age+1 where id=4;
Change table_name to the name of your table. Age is a column name, and the age is increased by 1. id=4 is telling mysql what row the update will affect. Remember I said earlier that you must have a primary key when creating a table. Well, the id is the primary key in this case, and it has a unique value for every record. It makes it easy to tell mysql what row you want to work with.

Another example

mysql> update table_name set bar='green' where first='3'
This changes the value of bar to "green", but only where the "first" column equals 3.

You can update multiple columns at once:

mysql> UPDATE table_name SET age=age+1,name='blah',city='Atlanta' where id=4;
Just separate the columns by commas.

Easy enough, eh?

Return to Top

RETRIEVING DATA FROM A TABLE

You use SELECT for obtaining data from a table. You will be using the SELECT statement a lot in mysql.

mysql> select * from table_name;

The * is a wild card, which means retrieve all data from table.

What if you want data in a specific column? To do that you use

mysql>select col_name from table_name WHERE col_name > 0;

Eg. you have a table called customerinfo with column names FIRSTNAME, LASTNAME, ADDRESS, EMAIL, and you want to get all the info about someone whose last name is mcdowell. Here's how:

mysql>select * from customerinfo WHERE lastname="mcdowell";

Note that under Windows9x, you can type your commands in any case you choose, since windows is case insensitive. Just remember to use the same case within the same query session.

Another example:

mysql> select FIRSTNAME, LASTNAME from customerinfo;
This will display all the names within the customerinfo table.

Return to Top

DELETING ROWS FROM A TABLE

mysql> DELETE FROM customerinfo WHERE ID=3;

Every row in a table is assigned an id by mysql beginning with 1 and increasing by 1 for every subsequent row. So if you want to delete a row, all you need is the row id as I've show in the example DELETE FROM customerinfo WHERE ID=3;

Return to Top

MODIFY COLUMN

mysql> ALTER TABLE table_name MODIFY column_name TEXT NOT NULL;

Let's say when you created your table, you chose a column type such as VARCHAR(15), that means the column will truncate any data longer than 15 characters. But now you don't want your data to be truncated. Well, the command above will do it. The use of TEXT means unlimited text.

Return to Top

RENAME COLUMN

mysql> ALTER TABLE table_name CHANGE col_name new_col_name TEXT NOT NULL;

You can rename a column as shown above with CHANGE. col_name is the current column name, while new_col_name will be what you want to rename the column to.

As you can see, you can use MODIFY only to change the column type, but CHANGE will let you rename the column. In addition, if you wanted to rename a column, as well as change the type, CHANGE will let you do that. In the example below, we want to rename the column, as well as change column type to varchar(25):

mysql> ALTER TABLE table_name MODIFY column_name new_col_name VARCHAR(25) NOT NULL;

Return to Top

DELETE/ADD COLUMN

mysql> ALTER TABLE table_name DROP COLUMN column_name;

This will remove column column_name

mysql> ALTER TABLE table_name ADD column_name INT UNSIGNED NOT NULL AUTO_INCREMENT;

This will add a new column with all the attributes you define.

Return to Top

DELETING A TABLE

mysql> DROP TABLE tbl_name

Note: the table and all it's data will be lost. So be careful!

Return to Top

RENAMING A TABLE

mysql> RENAME TABLE tbl_name TO new_table_name

Return to Top

DELETING A DATABASE

mysql> DROP DATABASE db_name;

Return to Top

USING PREFIXES

So far when we referred to a table, we have been using just the name alone. Eg. mysql> select * from table_name;

Another way of saying the same thing is

mysql>select * from database_name.table_name;

What that is saying is simply that we want to select all the data in the specified table, which is located in the specified database. Here's an example:

You have a database called mymusic and a table inside mymusic called cdcollection. To get data from the table you would use

mysql>select * from cdcollection;

or with prefix, you would use

select * from mymusic.cdcollection;

The two commands will do exactly the same thing.

But why would you want to use the more complex format, instead of the simpler select * from cdcollection;?

Here's why:

Assuming you have a database with two tables, one called cdcollection and another called mp3.

Also assume that each table has a column called artist. You want to retrieve data from both tables at once from the artist column. The only way to do that is to use prefixes like this:

mysql> select * from cdcollection.artist && mp3.artist;.

The && is nothing major. It's just mysql's way of saying and, just as || stands for or.

So,
mysql> select * from cdcollection.artist && mp3.artist;
basically means
mysql> select * from cdcollection.artist and mp3.artist;

Of course you should already be thinking about retrieving data from across databases in the same query. Eg.

mysql>select * from db1.table_name.col_name && db2.table_name.col_name;

Return to Top

DATABASE BACKUP

The importance of backing up your databases cannot be overemphasized. Most times you don't see a computer crash coming. It just happens, and your only hope then will be any backups you've made.

You can use mysqldump for your backups as follows:

In the query above, replace Username with the username you use to connect to mysql. Replace Password with your password. Note how there's no space between -p and Password. This is not a mistake, it should be typed that way, following the normal method of connecting to mysql. Mysql will not let you do anything on the server untill it has verified you're a valid user. Finally, replace dbName with the name of the database you want to backup.

Sample query. Assuming your username is matthisj, password is K2bbEsuNvx, and the name of the database is localusers. Then your query will be:

c:\mysql\bin > mysqldump -u matthisj -pK2bbEsuNvx localusers > c:\windows\desktop\1.txt This will create a txt file on your desktop called 1.txt. You of course, should put the file somewhere outside the current drive or computer. That way, if a crash occurs, you have a backup elsewhere. A tape drive, rewritable CD, another computer on a network, etc, are options.

What if you only want to dump just a single table from a database that contains many tables? Just add the name of the table after the database:

c:\mysql\bin > mysqldump -u Username -pPassword dbName tablename > c:\windows\desktop\1.txt

all_databases.sql is any file name and extension you choose.

Return to Top

RESTORING FROM A BACKUP

Use your backup file as input for mysql.exe.

The command should be self-explanatory, except maybe for the -e modifier. The -e means, --execute=... Execute command and quit.

What if you have a database with data, and you want to populate another database (located on a remote machine) with the same data?

c:\mysql\bin > mysqldump --opt -u username -pPassword database | mysql -u username -pPassword --host=remote-host-ip -C database

You must have a login account on the remote server of course. Let's say the ip address of the remote machine is 127.204.19.121, the database is called UserAccounts, username/password is malthus/theory. Your query then will be:

c:\mysql\bin > mysqldump --opt -u malthus -ptheory UserAccounts | mysql -u malthus -ptheory --host=127.204.19.121 -C UserAccounts

I used the command above to transfer a 65MB database to a remote server over the Internet. Here are the things I had to do to get it to work:

You can dump more than one database at once into a file by just listing the names of the databases. Also, you can dump all the databases on a server at follows:

c:\mysql\bin > mysqldump --all-databases > all_databases.sql

all_databases.sql is any file name and extension you choose.

One other error I have been encountering is this
MySQL Server has gone away
This happens when I have a data transfer that lasts a long time. On the server you're transfering from, add this to my.cnf, [mysqld] section:
max_allowed_packet = 128M
Restart mysql.
See this for ideas
http://stackoverflow.com/questions/12425287/mysql-server-has-gone-away-when-importing-large-sql-file

Return to Top

GETTING MORE HELP

Ok buddy, that ends my intro to MySQL. In the next tutorial, I'll show you how to start creating database driven websites using MySQL/PHP. Both programs are free, extremely powerful, and relatively easy to learn. For an example of what you can use php/mysql for, see what I have created - an Auto Webpage Generator and a Free-For-All-Links.

With the info contained in this tutorial, you should be on your way to mastering the language.

Munch On These!

DISCONNECTING FROM MYSQL Disconnect using one of these

mysql> quit

mysql> ctrl-D

Some More Queries: 

select version();
select now();
select (pi()/4), (6+3)/17;
select user();

Enter multiple queries on one line separated by semicolon, eg

select version(); select now();

Enter long statements on multiple lines, eg 

mysql> select 
    -> user()
    -> ,
    -> current date();

CANCEL COMMANDS B4 EXECUTION

Use \c Eg. 

mysql> select 
    -> user()
    -> ,
    -> current date()
    -> \c

What is wrong with this query?

mysql> select * from my_table where name= "smith and age < 30; 

You've left out a "
If you just press enter, you'll get
">
meaning you did not close the quotes. The best solution is to just cancel the query by entering "\c Note: just entering \c wouldn't cancel the query above

See databases you have with

SHOW DATABASES;

To select a database for use,

USE database_name;

Note: database_name is one of the databases shown under SHOW DATABASES

CREATING A DATABASE 

mysql> CREATE DATABASE database_name;
Eg.
mysql> CREATE DATABASE mp3;

CREATING TABLES After issuing command USE database_name; You then create tables in it.

CREATE TABLE mp3 (artist VARCHAR(20), title VARCHAR(20), tracks VARCHAR(30));

Note: artist, title, tracks are the column headings

View your table with DESCRIBE table_name;

Eg. DESCRIBE mp3;

LOADING DATA INTO A TABLE Two statements to use are LOAD DATA and INSERT

LOAD DATA reads data from a txt file

In the case of our table mp3, the columns are artist, title, tracks. So create a text file with one record per line. Note: line terminates when you use the carriage return. Separate each column data with tabs. Eg. 

R. Kelly
I don't see nothing wrong
track 1
track 2
track 3
A text file for the above will be R. Kelly I don't see nothing wrong track 1, track 2, track 3 Save text file, eg. mp3.txt

To load data from txt file, issue command

LOAD DATA LOCAL INFILE "file_name.txt" into table table_name;

When you insert a value of NULL (recommended) or 0 into an AUTO_INCREMENT column, the column is set to value+1, where value is the largest value for the column currently in the table.

syntax for a query asking for everything from a table EXCEPT where a specific field has a null value?

SELECT * FROM table WHERE NOT isnull(field);

Be aware of the differences between '', 0 and NULL.

The Maximum Value for a Column. Let's say the column is named article,
and table is called shop

SELECT MAX(article) FROM shop

You can also achieve the same result with

SELECT article FROM shop ORDER BY DESC LIMIT 1;

I have a huge table with entries on different subjects. Each subject is identified by $ident To search only rows belonging to $ident, I query as follows:

$query = "select * from music.comments where (ident = '$ident' && subject like '%$pattern%') ";
$query .= "|| (ident = '$ident' && name like '%$pattern%') || (ident = '$ident' && comment ";
$query .= "like '%$pattern%') ";

I've been asked many times how to randomly select data from a table.
Let's say we have a table with 5,000 rows, and we want to select two rows at random:

$query = "SELECT url, id*0+rand() AS shuffle ";
$query .= "FROM table_name ORDER BY shuffle DESC LIMIT 2 ";

"id" must be a column with unique numerical values. So in my case I used my AUTO_INCREMENT column (which is the primary key).

Another method is by using ORDER BY RAND():

SELECT column_name FROM table_name ORDER BY RAND() LIMIT x;

Order by rand() is a mysql function that randomly selects data from a table, then returns on the top x records. So assuming you wanted 5 records randomly selected from a table, you query will be:

SELECT column_name FROM table_name ORDER BY RAND() LIMIT 5;

Lets add another wrinkle. Let's say we only want rows where "url" has a value:

$query = "SELECT url, id*0+rand() AS shuffle ";
$query .= "FROM table_name WHERE url IS NOT NULL ORDER BY shuffle DESC LIMIT 2 ";

Note how we used IS NOT NULL. What if our query had been this:

$query = "SELECT url, id*0+rand() AS shuffle ";
$query .= "FROM table_name WHERE url != 'NULL' ORDER BY shuffle DESC LIMIT 2 ";

MySQL would have spit out an error message. You cannot compare null to anything else, since it has no value. You cannot even compare null to itself, eg. NULL = NULL is meaningless.

Another way to obtain random rows is to obtain results from mysql. The result returned can then be randomized using php's shuffle () function.

If you want to use the ID that was generated for one table and insert it into a second table, you can use SQL statements like this:

INSERT INTO foo (auto,text) VALUES(NULL,'text'); # generate ID by inserting NULL

INSERT INTO foo2 (id,text) VALUES(LAST_INSERT_ID(),'text'); # use ID in second table

Note: auto is an auto_increment, primary id column.

$connection = mysql_connect($server, $user, $pass);
$db = "music";
$query = "insert into music(title,id) values ('$values',null)";
$result = mysql_db_query($db,$query ,$connection);

$query = "INSERT into IDholder(login,last_id) VALUES";
$query .= "('$login',LAST_INSERT_ID())"; 
$result = mysql_db_query($db,$query ,$connection);
mysql_close ($connection);
Note how we closed the connection only once. If the connection had been closed after the first insert, mysql would have returned 0 for last_insert_id().

SenderRecipientMovedID
joygirl33compwizspace1
joygirl33fazzyspace2
wtbunnyjoygirl33space3
luuv11joygirl33space4

Assuming we want the records where the EITHER of two sets of conditions are true:

Condition 1: WHERE sender='joygirl33' && moved='space'
Condition 2: WHERE recipient='joygirl33' && moved='space'

Will the following query do it?

SELECT * FROM tbl WHERE recipient='joygirl33' || sender='joygirl33' && moved='space'

NO. What the query is going to do is select all records where
sender='joygirl33'

and all records where
recipient='joygirl33'

and all records where
moved='space'

Hence, the query will return all FOUR records. But only 3 records actually match.

Therefore, we must use parenthesis to separate the conditions as follows:

(recipient='joygirl33' && moved='space') || (sender='joygirl33' && moved='space')

MySQL Statements Overview
CommandClausesPredicatesOperatorsAggregate Functions
Create
Drop
Alter
Select
Insert
Update
Delete 		From
Where
Group By
Having
Order By
In 		Distinct
Top 		AND
OR
NOT
Between
Like 		Avg
Count
Sum
Max
Min

Another Angle
Return to Top

How to Run SQL Commands from a Text File

The mysql client typically is used interactively, like this:
shell> mysql database
However, it's also possible to put your SQL commands in a file and tell mysql to read its input from that file.
To do so, create a text file `text_file' that contains the commands you wish to execute.
Then invoke mysql as shown here:
shell> mysql database < text_file
You can also start your text file with a USE db_name statement.
In this case, it is unnecessary to specify the database name on the command line:
shell> mysql < text_file
If you are already running mysql, you can execute an SQL script file using the source command:
mysql> source filename;
For this to work, you need to put the file in the same directory as mysql.exe,
this is usually in /bin

Using mysql in Batch Mode
In the previous sections, you used mysql interactively to enter queries and view the results.
You can also run mysql in batch mode.
If you run a query repeatedly (say, every day or every week),
making it a script allows you to avoid retyping it each time you execute it.
Also, if you have a query that produces a lot of output, you can run the output
through a pager rather than watching it scroll off the top of your screen:
shell> mysql < batch-file | more
You can catch the output in a file for further processing:
shell> mysql < batch-file > mysql.out
To run mysql in batch mode, put the commands you want to run in a file,
then tell mysql to read its input from the file:
shell> mysql < batch-file
If you are running mysql under Windows and have some special characters in the
file that cause problems, you can do this:
dos> mysql -e "source batch-file"
If you need to specify connection parameters on the command line, the command might look like this:
shell> mysql -h host -u user -p < batch-file
Enter password: ********
Replace "host" and "user" with the proper values. Eg.
shell> mysql -h localhost -u root -p < commandfile.txt

Notice there was no password entered in the command. That's good to avoid someone seeing it over your shoulder. When you use mysql this way, you are creating a script file, then executing the script.
If you want the script to continue even if some of the statements in it produce errors,
you should use the --force command-line option.

Return to Top

Run a query across multiple databases

It's easy to retrieve data from two or more tables witin the same database. But what happens when you have two or more tables located in different databases? The answer is simple, you just append the database name to the table name:

databaseName.tableName.fieldName

Here is an example query from two databases: agents, and realestate

SELECT r.fname,r.lname,u.active FROM agents.reptable r LEFT OUTER JOIN realestate.users u ON u.login=r.rep

However for this to work, the user assigned to agents must have access to realestate, and vice versa. To do this, you just grant privileges to the users accordingly.

Return to Top

MySQL Replication

You can have an exact copy of your live database on one or more machines. The live database is called Master Server, and the other machines - Slave Servers. Changes are automatically made on the Slave servers as soon as they happen on the master server. Should your Master Server go down, you have multiple machines ready to take over.

First, check your firewall to make sure port 3306 is open on the master server.

Setup Master Server:

#vi /etc/my.cnf

Add this under [mysqld]

log-bin
server-id = 1

Save and exit.

If you have these in your my.cnf, make sure they're commented out
#skip-networking
#bind-address = 127.0.0.1

#mysql -u your_user -p

mysql> grant replication slave on *.* to repl@'%' identified by '<password>';

Change repl and password to something convenient for you.

mysql> quit

#/etc/rc.d/init.d/mysqld restart

Now we want to tar up the database, and copy it over to the slave.

#mysql -u your_user -p

mysql> FLUSH TABLES WITH READ LOCK;

The above will prevent all write queries.

mysql> quit

#tar -zcvpf /var/www/html/mysql-snapshot.tar.gz --directory /var/lib/mysql .

Basically, I've tarred up my mysql data directory (/var/lib/mysql), and placed it under my apache documents directory. That way, I can use wget to pull it down on the slave server.

#mysql -u your_user -p

mysql > SHOW MASTER STATUS;

You must write down the filename (usually someting like localhost-bin.000001), and the position number (mine was 3020). You will need these values when setting up the slave server.

Then you can allow writes again on your master by doing:

mysql > unlock tables;

mysql> quit

Setup the slave server:

#vi /etc/my.cnf

Add this under [mysqld]

log-bin
server-id = 2
log-slave-updates
log-warnings
replicate-ignore-db=mysql

Note: replicate-ignore-db=mysql will exclude your user database (mysql) from being replicated.

Save and exit.

#cd /var/lib/mysql

#wget http://your_master_server/mysql-snapshot.tar.gz

#tar -zxvpf mysql-snapshot.tar.gz

Restart slave server

#/etc/rc.d/init.d/mysqld restart

#mysql -u your_user -p

mysql> CHANGE MASTER TO MASTER_HOST='<master host name>', MASTER_USER='<replication user name>', MASTER_PASSWORD='<replication password>', MASTER_LOG_FILE='<recorded log file name>', MASTER_LOG_POS=<recorded log offset>;

Change all the values in <> to the actual values.

master host name: This is the domain name or ip address of your master server, e.g. oasisoflove.com
replication user name / replication password: These are the values you entered when you issued "grant replication slave" on the master server.
recorded log file name / recorded log offset: The values you wrote down when you issued SHOW MASTER STATUS on the master.

mysql> START SLAVE;

If you don't get any errors, the data directory will contain a file called `master.info' and one called `relay-log.info'. These two files are used by the slave to keep track of how much of the master's binary log it has processed. Do not remove or edit these files.

Issue the command

mysql> SHOW SLAVE STATUS \G

Now, to test your setup, make some changes to the database on the master. Check the slave to see if the same change happened.

You can create more slaves the same way you did the first. Just make sure server-id is unique for all slaves, and not equal to the master server-id.

Finally, you can write your scripts such that they read from the slaves, and write only to the master.
Example mysql read connection:

$server = array(
"localhost",
"232.128.16.95",
"75.1.45.223",
"55.30.158.63"
);

shuffle($server);

$user = "your_db_user";
$pass = "your_user_pass";

foreach($server as $randServer) {

	$connection = mysql_connect($randServer, $user, $pass) or $connection = false;

		if ($connection!=false) {

			break;
		}
}

Each time a READ operation needs to be performed, a random server is chosen from the array.

If your master goes down, you can promote one of your slave machines to a master by doing:

mysql> CHANGE MASTER TO MASTER_HOST='<new master host name>'

where "new master host name" is your new master.

STOPPED SLAVE
What happens if your slave stops replicating?

Start go to the master and delete all bin log files, e.g. localhost-bin.000001, localhost-bin.index, localhost-bin.000002, etc. Then start from FLUSH TABLES WITH READ LOCK above and follow the steps.

NOTES:

While the slave is running, do not run any queries on the slave that would result in updates to the database. If you introduce inconsistencies betweeen the master and slave, the slave thread will quit the replication process.. For example, if you delete a database or a table on the slave, queries from the master involving that database can no longer be run on the slave. This will cause the slave thread to quit.

If your slave doesn't seem to be replicating, take a look in your mysql error logs (/var/log/mysqld.log, or check /etc/my.cnf for where the error log is).

Return to Top

Reset root password that you forgot

[root@host root]#killall mysqld

[root@host root]#/usr/libexec/mysqld -Sg --user=root &

You may have better luck with:

mysqld --skip-grant-tables --user=root

Go back into MySQL with the client:

[root@host root]# mysql

Welcome to the MySQL monitor. Commands end with ; or g.

Your MySQL connection id is 1 to server version: 3.23.41

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

mysql> USE mysql

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> UPDATE user

-> SET password=password("newpassword")
-> WHERE user="root";
Query OK, 2 rows affected (0.04 sec)
Rows matched: 2 Changed: 2 Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> exit;
[root@host root]#killall mysqld

Start MySQL the normal way for your system, and all is good. For Red Hat this is:

/etc/init.d/mysqld start

Allow a user to create a database

The CREATE permission allows a user to create databases. E.g.
grant create on `db_name`.* to user_name@localhost identified by 'some_password';

Even though the user now has CREATE permission, it's only on the database db_name. So the user still cannot create a new database.

To be able to create a database, we must use a wild card.

grant create on `%\_xyz`.* to user_name@localhost identified by 'some_password';

grant create on `xyz\_%`.* to user_name@localhost identified by 'some_password'; 

The % is a wildcard.
The first command would allow user create any database named:
db23_xyz
users_xyz
etc.
The user won't be able to create
db23_abc
usersxyz

User only has permission to create a db with a name ending in _xyz.

If you run the second command, user can create:
xyz_users

but not:
xyzusers.

User only has permission to create a db with a name starting with xyz_.

Further reading

http://dev.mysql.com/doc/refman/5.0/en/replication-howto.html
http://www.ced.univ-montp2.fr/mysql/manual-split/manual_Replication.html