MySQL Administration Server Files and Scripts

From Training Material
Jump to navigation Jump to search
MySQL Administration Server Files and Scripts
Bernard Szlachta (NobleProg Ltd)

MySQL Server Files and Scripts

  • MySQL Programs
  • Server Programs
  • Client Programs
  • GUI and other tools

MySQL Programs


  • mysqld - the MySQL server
  • mysqld_safe - a script which starts mysqld, recommended of starting the server
    • restarts the server when an error occur
    • logs run-time and failure information
  • mysqld_multi - script that can start or stop multiple servers installed on the system (different ports or sockets)


  • mysql - a command line client
  • mysqladmin - administrative operations
    • relading the grant tables
    • creating/dropping database
    • flushing tables to disk
    • managing log files
    • shutdown the database
  • mysqlcheck - repairs, analyzes and optimizes tables
  • mysqldump - dumps into SQL, text or XML files

MySQL Programs

TODO: update it to current version 5.6 or newer

Ways of starting a server

Windows or Unix service

  • sudo /etc/init.d/mysql start


  • sudo mysqld_safe

Command line mysqld

  • sudo mysqld

Stopping MySQL Server

Windows/Unix Service

  • sudo /etc/init.d/mysql stop
  • sudo service mysql stop
  • net stop mysql (Windows)


  • mysqladmin -u root -p shutdown

Changing Forgotten Password

mysqld --skip-grant-tables
# server starts without using the privilege system at all
mysqladmin flush-privileges or 
mysqladmin reload or
# Change your password
set password  for root@localhost = password('asdfasdf')

Changing Forgotten Password

In order to prevent external connections accessing the databases you can change the port and the bind interface

mysqld --bind-address= --port 333 --skip-grant-tables

You can connect to the database with the command below

mysql -P 333 -h

Running Scripts

Execute a script

mysql> source filename
cat filename | mysql               (Unix only)
type filename | mysql             (Windows only)
mysql < filename
GUI Tools

Running a SQL statement from shell

echo "select * from EMP;" | mysql test;

Saving errors to a file

mysql  --force < tables.sql  2> error.log

Using Client Tools

System Variables

SET USER=your_name (Windows), in Unix the currently logged in user in the system
mysql –u user –ppassword –h host database

Config file (my.ini or /etc/mysql/my.cnf)