Guide for beginners and administrators


 

Running a test in the command line: MySQL monitor

 

C:\web\mysql\bin>mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.18-nt

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

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
3 rows in set (0.03 sec)

mysql> exit
Bye

C:\web\mysql\bin>

The following example shows how to start MySQL monitor under the root user with empty password.

Shows all databases.

Closes MySQL monitor

If mysql is not working properly, an error message appears:

ERROR 2003 (HY000): Can't connect to MySQL server on 'localhost' (10061)

 

Shutting Down and Uninstalling the Server

NET STOP MySQL (shuts the service down)

mysqld --remove (removes the service)

mysqladmin -u root shutdown (shuts down the server / service)

 

C:\web\mysql\bin>mysqladmin -u root shutdown
060316 20:53:47 [Note] mysqld-nt.exe: Normal shutdown

060316 20:53:49 InnoDB: Starting shutdown...
060316 20:53:51 InnoDB: Shutdown completed; log sequence number 0 411079
060316 20:53:51 [Note] mysqld-nt.exe: Shutdown complete

(c:\web\mysql\bin\mysqladmin -u root shutdown)

 

  • Uninstallation I: Go to Control Panels - Add or Remove Programs - uninstall MySQL. It is recommended that you backup all data files and dabases before uninstalling. The configuration files in the root or Windows directory (my.ini) will not be removed during the uninstallation.
  • Uninstallation II: Go to command line and remove the service, then delete the installation directory.

Managing the server with MySQL Administrator

www.mysql.com/products/tools/administrator/

  • The MySQL Administrator program enables you to easily administer your server in graphical enviroment (when I was writing this article, the latest version was: mysql-administrator-1.1.9-win.msi).

  • After you download the files from internet and install, I recommend that you run MYSQL System Tray Monitor from the Start menu.

  • Icon : MySQL System Tray Monitor - the icon in the right bottom corner notifies of a successful application start. It shows the server status in graphics.

 

  • An example for installation of a new service: MySQL System Tray Monitor - click the right mouse button - Action - Manage MySQL Instances

  • Service Control - Configure Service

  • Button: Install New Service

  • Enter a name for the new service: MySQL (Example).. OK

  • Check the location of my.cnf (my.ini). The default setup is usually not correct. I chose c:\windows\my.ini

  • I changed "Path to binary" to C:\web\mysql\bin\mysqld-nt

  • I confirmed the changes by clicking the "Apply Changes" button

  • After having gone through this process, the service is installed and you can start it or it starts automatically on Windows start (according to the settings).

  • Examples of other tools for MySQL administration: www.mysqlfront.de, www.phpmyadmin.net, www.sqlmanager.net, www.mydb-studio.com

Installing more servers on a computer

  • You can run several servers on a computer. For each server you have to adjust the configuration, set data destinations, service names and port numbers.

my.ini

[mysql]
basedir=C:/mysql
#bind-address=127.0.0.1
datadir=C:/mysql/data
#port=3306

[mysqls4]
basedir=C:/mysql4
#bind-address=127.0.0.1
datadir=C:/mysql4/data
port=3307

[mysqls5]
basedir=C:/mysql5
#bind-address=127.0.0.1
datadir=C:/mysql5/data
port=3309
  • Separate particular services in my.ini file by their names in braces [name_of_service]. There must be a unique name, directory and port for each service.

my_1.cnf

[mysqls41]
basedir=C:/mysql41
#bind-address=127.0.0.1
datadir=C:/mysql41/data
port=3308
#socket=MySQL
skip-locking
set-variable = key_buffer=16K
set-variable = max_allowed_packet=1M
set-variable = thread_stack=64K
set-variable = table_cache=4
set-variable = sort_buffer=64K
set-variable = net_buffer_length=2K
server-id = 1

  • You can install each service together with a separate configuration file, e.g. our my_1.cnf, which includes detailed settings unrelated to other services.

Batch file for installing and starting servers

  • There exist 4 folders on the disk. In the first one - mysql - we have pre-created configuration files and batches. The first one is an installation batch that copies the files to the corresponding directories, installs the services and assigns configuration files (--defaults-file=c:\my_1.cnf) if you have the mysqls41 service. For the first time, the services start after the installation and then run automatically.

 

copy c:\mysql\my.ini c:\windows
copy c:\mysql\my_1.cnf c:\

c:\mysql\bin\mysqld-nt --install
c:\mysql4\bin\mysqld-nt --install mysqls4
c:\mysql41\bin\mysqld-nt --install mysqls41 --defaults-file=c:\my_1.cnf
c:\mysql5\bin\mysqld-nt --install mysqls5
net start mysql
net start mysqls4
net start mysqls41
net start mysqls5

 

Batch file for shutting down and uninstalling servers

  • This batch shuts the services down, removes them and deletes configuration files.

net stop mysql
net stop mysqls4
net stop mysqls41
net stop mysqls5

c:\mysql\bin\mysqld-nt --remove
c:\mysql4\bin\mysqld-nt --remove mysqls4
c:\mysql41\bin\mysqld-nt --remove mysqls41
c:\mysql5\bin\mysqld-nt --remove mysqls5

del c:\windows\my.ini
del c:\my_1.cnf



FAQ (Frequently Asked Questions)

Encyclopedia

 

Web links

Search



for Webmaster About us

Copyright © 2000 - 2016 AdminXP.com - R 0.996