MySQL Administration Server Files and Scripts

title

MySQL Administration Server Files and Scripts

author

Bernard Szlachta (NobleProg Ltd)

MySQL Server Files and Scripts

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

MySQL Programs

SERVER

• 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)

CLIENT

• 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

Mysqld_safe

• 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

• 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
FLUSH PRIVILEGES
# 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=127.0.0.1 --port 333 --skip-grant-tables

You can connect to the database with the command below

mysql -P 333 -h 127.0.0.1

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_TCP_PORT=3306; export MYSQL_TCP_PORT
mysql –u user –ppassword –h host database

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

[client]
host=host_name
user=user_name
password=your_pass