- Setup Type: the default is Typical, which will be sufficient for our purposes.
- Several other panels follows which you can go through, and then
See Wizard Completed with these options, then
Configure the MySQL server now
Configure now. You can reconfigure it through a program menu item later as well.
Register the MySQL Server now
Uncheck it now. If you want, you can do it later.
- Select Configuration Type: take the default, Detailed Configuration. It's a good idea just to see the choices that you're making even if you take the defaults.
- Server Type: probably the default, Developer Machine.
- Database Usage: probably the default, Multifunctional Database.
- InnoDB Tablespace Settings: Take the defaults.
- Concurrent Connections Settings: Take the default, Decision Support (DSS/OLAP)
- Networking Options: Keep the defaults, Enable TCP/IP Networking and Enable Strict Mode.
- Default Character Set: probably the default, Standard Character Set.
- Windows options:
You want both, so check the second checkbox.
Install As Windows Service Include Bin Directory in Windows PATH
- Security Options:
Uncheck this checkbox to maintain
the initial empty root password.
Modify Security Settings
- Ready To Execute: Go for it! Press the Execute button. If the "Start Service" fails, go back to the Windows Options panel and select a different name for the Windows service. If the Apply security settings fails, it may be that you already have a password.
\Program Files\Mysql\MySQL Server 5.5The actual databases reside in a subfolder named data, typically in an "Application Data" folder:
\Documents and Settings\All Users\Application Data\MySQL Server 5.5\dataThe Windows Programs menu contains the item:
- MySQL command-line client: (accessses the command-line mysql as administrative)
Control Panel → System → Advanced → Environment VariablesAfter the installation MySQL will probably start up automatically as a background service. Perhaps the easiest way to get to Services is to key this word into the Search Box of the Run menu. Look for MySQL in the list of services, the startup type should be "automatic" and it should be "started" after installation.
Command line management of MySQLThe binaries in the bin subfolder of the installation folder. Hopefully MySQL has already installed this folder in your PATH environment variable. If not, add it through the system Environment Variables (use the Search Box).
$ mysql -u root mysql> show databases; mysql> create database test; (it may already exist) mysql> show databases; mysql> select user,host,password from mysql.user; mysql> create user guest@localhost; (it may already exist) mysql> select user,host,password from mysql.user; mysql> select user,host from mysql.db; mysql> grant all on test.* to guest@localhost; mysql> select user,host from mysql.db; mysql> quitTest the effectiveness by accessing the test database as the guest user:
$ mysql -u guest testIf for some reason this doesn't work, try forcing a reload of the administrative database:
$ mysqladmin -u root reloadCompare the differences in priviledges between the root and guest users (the \G statement terminator is used to field information in list form instead of tabular form):
$ mysql -u root mysql mysql> select * from user where user='root' and host='localhost' \G mysql> select * from user where user='guest' and host='localhost' \G
SQL syntax learning examplesHere are some examples you can use to help learn basic SQL commands. The test database accessible by the guest user without password is assumed. Start a command shell and, from the command-line, execute:
$ mysql -u guest mysql> use test; mysql> create table things (thing varchar(10), qty int); mysql> show tables; mysql> describe things; mysql> insert into things values ( 'book', 10 ); mysql> insert into things values ( 'pencil', 4 ), ( 'book', 5 ); mysql> select * from things; mysql> delete from things where thing='book'; mysql> select * from things; mysql> insert into things values ('table', 2), ('chair',12); mysql> select * from things; mysql> update things set qty=qty-1 where qty>10; mysql> select * from things; mysql> update things set qty=qty+1 where thing='table'; mysql> select * from things; mysql> drop table things; mysql> quit
Using passwords with mysql clientIf a user access is password-protected, the mysql client requires the usage of the -p option to provide the password. This can be done in one of two ways:
- The simple, unadorned -p option, with prompt:
$ mysql -p -u .... Enter password: MY-PASSWORD
- The -p option with password appended
(useful for testing, but not a good idea in general):
$ mysql -pMY-PASSWORD -u ....
Using Cygwin for shell accessIf, like me, you prefer the Cygwin shell, you can install the mysql package in Cygwin, but you don't want to run the server there. In that case, the easiest way to your MySQL database through the Cygwin mysql client is through the network interface like this:
$ mysql --protocol TCPTo simplify and just use mysql, add this line to your ~/.bashrc file:
alias mysql='mysql --protocol TCP'Navicat. This site, per se, no longer offers its free "lite" version as of 2011, but apparently it is still alive to some degree, and well worth the installation. The MySQL company offers MySQL Workbench although I find it somewhat confusing to use. If you have an Apache/MySQL/Php installation, the most well-known tool is phpMyAdmin.
Navicat LiteAssuming you can find it somewhere, the Navicat lite version can be freely downloaded and used for non-commercial purposes. Here it is on the course web site:
- Select MySQL from the choices.
- Set the Connection Name to root. Everything else should be in place already (assuming an empty root password).
- Click the Test Connection button to double-check.
- Make sure the Save Password checkbox is checked.
Creating a MySQL users with a passwordIf you want to create a user some_user with the password some_password, the replacement command would be this:
mysql> create user some_user@localhost identified by 'some password';
Establishing priviledges for a mysql userWe saw above how the mysql command-line interpreter can grant priviledges by which users can access the databases. Here are some other examples:
To create a new priviledged user, priv@host
(host the desired entry host):
$ mysql -u root mysql> grant all on *.* to priv@host;
To give a user restricted@host
"read only" priviledges on somedb, we replace
the "all" in grant by the desired restricted access:
$ mysql -u root mysql> grant select on somedb.* to restricted@host;
MySQL Command-line administrationMySQL software provides a number of useful commands to manipulate its databases, including:
- mysqladmin: basic administration commands
- mysqldump: dump the contents of table(s) in a database
- mysqlshow: show table/fields in a database
- mysql command line interpreter for entering SQL commands
$ mysql[TAB][TAB]For example, try these commands:
$ mysqlshow -u root $ mysqlshow -u root mysql $ mysqlshow -u root mysql user $ mysqldump -u root mysql userTo ensure that changes made to the mysql database are not picked up, do one of this::
$ mysqladmin -u root reloadThere's also the "refresh" command which has a somewhat different outcome.
Backup and reloadThe mysql commands provide an excellent scheme by which a database can be "backed up" and then "reloaded". Do the backup like this:
$ mysqldump -u root somedb > somedb.sqlThe somedb.sql file contains the data in all the tables plus the commands needed to recreate these tables. A restoration from an non-existent database would be done like this:
$ mysql -u root mysql> create somedb mysql> use somedb mysql> source somedb.sql
MySQL access principlesMySQL is a network-oriented DBMS. Client programs may reside on different hosts than the server. The access rights of MySQL client has to a MySQL DBMS database is determined by three factors:
- the user specified by the client
- the host on which the client operates
- the database that the client is trying to access
- user table:
When the client on host connects to the MySQL server, the pair
(user, host)is matched against the (user,host) values in the rows of the user table. If no such user or host exists, an empty is used to match. If the password field is non-empty the client must provide the password. The client obtains global priviledges from the remaining fields in the record.
- host table:
When a client on host attempts to use a specific database, the pair:
(host, database)is used to match against the entries in the host table, and thereby augment the priviledges available to the client when accessing from this host.
- db table:
Each database, with user information is listed in the db table.
(user, database)is used to match against the entries in the db table, and therefore further augment the priviledges available to the client as this particular user.
$ mysql -u root mysql mysql> describe user; mysql> select user,host,password from user; mysql> describe host; mysql> select host,db from host; mysql> describe db; mysql> select user,db from db; mysql> select * from db where db='test' \G (mostly 'Y')The root user has all global priviledges and the anonymous user has none. Nevertheless, the anonymous user gains priviledges for the test database by matching an entry in the db table.
192.168.10.100The client is external and wants to use this MySQL service on this server. Here are the steps necessary:
- Deal with the firewall
If you have a firewall running, then somehow or another, you need to get
through to the mysql port (3306) from the outside. On Ubuntu, using the
ufw firewall management, look at the output of
$ sudo ufw statusto see if there is a firewall, and if it is open for the mysql port (with TCP). If not, you can open it by doing:
$ sudo ufw allow mysql/tcp
Have MySQL listen on the external network interface
By default the MySQL server only listens on an internal socket and the localhost network
interface. As root, edit the MySQL configuration file,
/etc/mysql/my.cnf and look for the
line for the server:
[mysqld] ... bind-address = 127.0.0.1Right underneath that line, add this one:
bind-address = 192.168.10.100We need to restart the service for this to take effect, but we can do it after the next change.
- Give access to a user from external host
The guest user we created above only has access from localhost.
We need access from all external clients of interest.
The easiest way is to make a guest user with access from any host:
$ mysql -u root mysql> drop user guest@localhost; (if you have already added it) mysql> create user guest; (unspecified host means any) mysql> grant all on test.* to guest; mysql> select user,host,password from mysql.user; (host for the guest user is %)
$ sudo restart mysqlIf you have the mysql command-line client program on the client, you would use this form to access the MySQL DBMS on the server:
$ mysql -h 192.168.10.100 -u guest test