MySQL Installation and FAQ
All of the information in this tutorial besides the installation instructions can be found at http://mysql.com/doc. Use the search at the top of the page to find what you're looking for (The documentation is very good).
How do I install MySQL from source?
How do I reset my MySQL root password?
How do I backup or restore my MySQL databases?
How do I add users to MySQL?
Installing MySQL from source tarball
1. Download source from mysql.com
2. mv mysql-x.x.xx.tar.gz /usr/local/src
3. cd /usr/local/src
4. tar -zxf mysql-x.x.xx.tar.gz
5. cd mysql-x.x.xx
Now that we have the tarball unpacked where we want it, start the build process...
./configure --prefix=/usr/local/mysql
This will put it in it's own directory, which I always put things in /usr/local as a standardization. Run the make command:
make
Run the make install command:
make install
Now it is installed. You need now to set it up. Add the MySQL Libraries to the ldconfig file:
echo "/usr/local/mysql/lib/mysql" >> /etc/ld.so.conf
Now have it cache all of the libraries:
ldconfig -v | grep libmysqlclient
You should now see something returned like:
libmysqlclient.so.6 => libmysqlclient.so.6.0.0
Now have MySQL start at boot time:
echo "/usr/local/mysql/bin/safe_mysqld > /dev/null &" >> /etc/rc.d/rc.local
Now you must make the initial databases the MySQL needs to run:
./scripts/mysql_install_db
Now start up the service daemon for MySQL:
/usr/local/mysql/bin/safe_mysqld > /dev/null &
Now add it to the path:
PATH="$PATH:/usr/local/mysql/bin"
Now you should test MySQL:
mysqladmin version
This should give you back all kinds of info on MySQL. Now create a password for the SuperUser:
mysql -u root -p
It will then prompt your for a password:
Enter password:
And MySQL should be setup.
How do I reset my MySQL root password?
What? You forgot your MySQL rootpass? *shame* *shame*
Follow these simple steps to reset your password
Take down the mysqld server by sending a kill (not kill
-9) to the mysqld server. The pid is stored in a `.pid'
file, which is normally in the MySQL database directory:
shell> kill `cat /mysql-data-directory/hostname.pid`
You must be either the Unix root user or the same user mysqld runs as to do this.
Restart mysqld with the --skip-grant-tables option.
Connect to the mysqld server with:
shell> mysql -u root mysql
Issue the following commands in the mysql client:
mysql> UPDATE user SET Password=PASSWORD('mynewpassword')
-> WHERE User='root';
mysql> FLUSH PRIVILEGES;
After this, you should be able to connect using the new password.
You can now stop mysqld and restart it normally.
More information can be found at
http://mysql.com/doc/en/Resetting_permissions.html
How do I backup or restore my MySQL databases?
This is actually a pretty simple thing to take care of. Mysql comes with a handly little program called 'mysqldump'. Everything you need to know about mysqldump can be found in the manpage (i.e. 'man mysqldump').
Here's a quick overview:
This command will dump all mysql data and table structures to the file mysql-backup.sql
shell% mysqldump --all-databases > mysql-backup.sql
If you don't want *all* mysql databases, you can specify one or more databases to be dumped, as such:
shell% mysqldump --databases database1 [database2 ...] > my_databases.sql
Now, to restore your *.sql files to a mysql server, you just use the following command:
shell% mysql < mysql-backup.sql
or, if just a single database:
shell% mysql dbname < dbname.sql
How do I add users to MySQL?
The following table shows the privilege structure of MySQL. Each user has the ability to have access to each of the privileges listed. Any of the privs that show "Table or Database" access can be given on a per table or db basis.
| Table Field | Type of Privilege | Possible Values |
|
Host | Connection access | Hostname to connect from
(% is a wildcard) |
|
User | Connection access | Mysql username |
|
Password | Connection access | PASSWORD('yourpass') |
|
Select_priv | Tables | Y / N |
|
Insert_priv | Tables | Y / N |
|
Update_priv | Tables | Y / N |
|
Delete_priv | Tables | Y / N |
|
Create_priv | Databases or tables | Y / N |
|
Drop_priv | Databases or tables | Y / N |
|
Reload_priv | Server Administration | Y / N |
|
Shutdown_priv | Server Administration | Y / N |
|
Process_priv | Server Administration | Y / N |
|
File_priv | File access on server | Y / N |
|
Grant_priv | Databases or tables | Y / N |
|
References_priv | Databases or tables | Y / N |
|
Index_priv | Tables | Y / N |
|
Alter_priv | Tables | Y / N |
Now, I prefer to manually edit the grant tables to add/remove users and change privileges. Remember that anytime you make changes to the grant tables, you must do a "flush privileges;" at the mysql prompt for the changes to take effect.
Take the following example, which will create user "monty" who is able to connect to the mysql server from "localhost" with the password "some_pass" and has unrestricted access to the server.
shell% mysql -uroot -p mysql
Password: (enter rootpass here)
mysql> INSERT into user VALUES('localhost','monty',PASSWORD('some_pass'),
-> 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
mysql> FLUSH PRIVILEGES;
To see what privileges monty has, you can use the following query:
mysql> SELECT * from user where user='monty';
Now, if you would like to give monty access to the "bankaccount" database, you can use the following:
mysql> INSERT INTO db
-> (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,
-> Create_priv,Drop_priv)
-> VALUES
-> ('localhost','bankaccount','monty','Y','Y','Y','Y','Y','Y');
mysql> FLUSH PRIVILEGES;
More information on this topic can be found at
http://www.mysql.com/doc/en/Adding_users.html
|
