MySQL学习笔记2-System administration

System administration
=======================
MySQL Server Distributions


MySQL is available for several operating systems, including Linux, Windows, Mac OS X, and Oracle Solaris


MySQL is available as both binary and source distributions:
o Binary distributions: Are precompiled, ready-to-run programs that are available for Enterprise and Community MySQL Server versions. These binaries are the official Oracle-tested versions.
o Source distributions: Are not guaranteed to be consistent with commercial code updates, nor do they include Oracle support 
The slide shows only some of the available operating systems. 
Refer to the MySQL downloads website for a complete list of available OS types: 
http://dev.mysql.com/downloads/mysql/. 
See the MySQL Reference Manual for installation instructions for each OS: 
http://dev.mysql.com/doc/mysql/en/installing.html.


MySQL Binary Distributions


Binary for Linux
o RPM files are available for RPM-based Linux distributions, such as Oracle Linux. These files are installed by using the rpm program, or by using a package manager such as yum. The installation layout for each RPM is given by a specification file contained within the RPM file itself. (Use rpm -qpl <rpm_file> to determine where the contents of an RPM file are located upon installation.) 
Note: See http://rpm.org/ for more information about RPM files.
o TAR files are available for many varieties of Linux and UNIX-like systems. To install this kind of distribution, unpack it using the .tar program in the installation directory.
o For more information about specific packages available for Linux distributions, see http://dev.mysql.com/downloads/mysql/. 
Binary for Windows
o Complete distribution: Contains all files for a MySQL installation, as well as the configuration wizard
o No-install distribution: A .zip archive for which no installation or configuration wizard is used. You simply unpack it and move it to the desired installation location.


MySQL Source Distributions
A source distribution can be installed at any desired location. The default Linux installation location is /usr/local/mysql.
You can find the basic commands to install a MySQL source distribution in the MySQL Reference Manual: http://dev.mysql.com/doc/mysql/en/source-installation.html.


MySQL RPM Installation Files for Linux
The recommended way to install MySQL on RPM-based Linux distributions is by using the RPM packages, in the form of .rpm files. RPM files are provided for many platforms, such as Oracle Linux, SuSE, and other Linux versions. Many more distributions are available for other types of *nix systems, including Oracle Solaris, FreeBSD, and Mac OS X. Oracle provides two types of MySQL RPMs:
o Distribution-independent: The RPMs that MySQL provides to the community, which should work on all versions of Linux that support RPM packages and use glibc 2.3
o Distribution-specific: Intended for a targeted Linux platform


Linux MySQL RPM Installation Process
To install the RPM files and start the server:
1. Download the appropriate RPM files (for your Linux version) from the MySQL downloads website.
2. Install all the downloaded RPM files.
a. Execute the rpm -i <rpm_filename> command for each RPM file.
b. The installation performs the following tasks automatically as it runs:
o Extracts RPM files to their default locations
o Registers a startup script named mysql in the /etc/init.d directory
o Executes mysql_install_db, a script that creates the system databases and default my.cnf file, sets up a random password for the root accounts, and saves that password in the installing user’s home directory in a file called .mysql_secret
o Sets up a login account with user and group names for mysql (for administering and running the server)
3. Start the MySQL server.
a.Execute the /etc/init.d/mysql start command to start the MySQL server.


Linux MySQL Server Installation Directories
When the RPM files are unpacked, many files are automatically extracted and placed into different locations. The slide shows the most common default directory locations and files.
o Data Directory
/var/lib/mysql is where the server stores databases. This directory is preconfigured and ready to use. For example, this directory includes a mysql subdirectory (contains the grant tables) and a test directory for the test database (can be used for testing purposes). The InnoDB log files and system tablespace are in this directory.
o Base Directory
/usr is the base directory. It contains program files and a my.cnf file. When you use mysqld_safe to launch the MySQL Server process (mysqld), it uses the my.cnf file stored in this location.
/usr/bin contains client programs and scripts such as mysql, my_print_defaults, mysqladmin, mysqlcheck, mysqld_safe, and innochecksum.
o Other Directories
/etc and /var/log are standard Linux directories for configuration files and log files. The /etc/my.cnf file is read by the MySQL Server process (mysqld). For more information about the use of configuration options files see the lesson titled “Server Configuration”.


Starting the MySQL Server on linux
The server can be started on Linux using several methods:
o mysql.server: Used as a wrapper around mysqld_safe for systems such as Linux and Oracle Solaris that are using System V run-level directories
o mysqld_safe: Sets up the error log and then launches mysqld and monitors it. If mysqld terminates abnormally, mysqld_safe restarts it. If the server does not start properly, look in the error log.
o mysqld: Invokes the server manually to debug the MySQL server. The error messages go to the terminal by default rather than to the error log.
o mysqld_multi: Perl script that is intended to simplify the management of multiple servers on a single host. It can start or stop servers, or it can report whether servers are running. 
Install the correct script to have the server run automatically at startup:
o On BSD-style Linux systems, it is most common to invoke mysqld_safe from one of the system startup scripts, such as the rc.local script in the /etc directory.
o Linux and UNIX System V variants with run-level directories under /etc/init.d use the mysql.server script. Prebuilt Linux binary packages install mysql.server under the name mysql for the appropriate run levels. Invoke it manually with an argument of start or stop, either directly or by using the service command.


Stopping the MySQL Server on linux
To stop the server manually, use one of the following techniques:
o mysqladmin: Has a shutdown command. It connects to the server as a client and can shut down local or remote servers.
o mysql.server: Stops and/or shuts down the local server when invoked with an argument of stop
o mysqld_multi: Stops and/or shuts down any of the servers that it manages. It does so by invoking mysqladmin 
mysqld_safe has no server shutdown capability. You can use mysqladmin shutdown instead. Note that if you forcibly terminate mysqld by using the kill -9 command to send it a signal, then mysqld_safe detects that mysqld terminated abnormally and restarts it. You can work around this by killing mysqld_safe first and then killing mysqld, but it is better to use mysqladmin shutdown, which initiates a normal (clean) server shutdown.




Improving Installation Security
When you install MySQL from an RPM package, it sets up a random password for the root account and saves that password to the .mysql_secret file in the installing user’s home directory. For all other installations, the initial passwords are blank.
As a result, you should set up passwords as soon as the server has been started.
Invoke mysql_secure_installation without arguments, which prompts you to determine which actions to perform.
For a full discussion of the postinstallation procedure for UNIX operating systems, see http://dev.mysql.com/doc/mysql/en/unix-postinstallation.html.


Windows MySQL Server Installation Directory


By default, MySQL 5.6 is installed at the directory path C:\Program Files\MySQL\MySQL 5.6 Server. Bin Directory \bin contains the MySQL server and client programs. Windows MySQL distributions include multiple programs, which can be found in the bin directory:
o mysqld.exe: Standard server
o Other client programs, such as mysqladmin.exe (see the description in the Linux section) 
Data Directory 
\data is where the server stores databases and log files. This directory is preconfigured and ready to use. For example, this directory includes a mysql subdirectory (contains the grant tables) and the test subdirectory for the test database (can be used for testing purposes). 
Configuration File 
The my.ini configuration option file specifies where the installation directory is located, as well as other optional settings.


Running MySQL on Windows
Run a Windows MySQL server manually from the command line of a console window by using the mysqld command (with extra options, if needed). You stop the server by using the mysqladmin shutdown command.
To run the MySQL server so that Windows itself starts and stops the server when Windows starts and stops, install the server as a Windows service. To do this, invoke the server from the command line using the mysqld --install command (with extra options if needed).
You can also start and stop the service manually from the command line by using the net start MySQL and net stop MySQL commands.
To start and stop the service by using the Windows Services GUI, select the MySQL service in the Administrative Tools and then click the Start or Stop link. You can configure manual or automatic startup in the Services GUI.
To remove the service after it has been stopped, use mysqld with the --remove option.


Data Directory
Parts of the structure are specific to the storage engine. However, every table has a *.frm file (including views) regardless of the storage engine being used. The MySQL server changes its current working directory to its data directory when it begins executing. 
You must make sure that the MySQL server has the proper access rights to create files in the data directory. The server must have access rights in any directory where it needs to create data files or log files. 
The MySQL server maps each database to a directory under the MySQL data directory, and by default it maps tables in a database to file names in the database directory. This has the following implications:
o Database and table names are case-sensitive in the MySQL server only on operating systems that have case-sensitive file names (such as most Linux systems).
o You can split the disk usage by moving the data directory, databases, and/or single tables (depending on the storage engine options) to different physical locations, which can improve performance.


Execute Upgrade Program
o mysql_upgrade: Reads options from the command line and from the [mysql_upgrade] and [client] option file groups 
Check and Repair Tables Run mysql_upgrade to:
o Check all tables in your databases for incompatibilities with current versions of the MySQL server
o Repair any problems found in tables with possible incompatibilities
o Upgrade system tables to add any new privileges or capabilities that are available in the new version
o Mark all checked and repaired tables with the current MySQL version number 
To check and repair tables and to upgrade the system tables, mysql_upgrade executes the mysqlcheck command. 
For more information about usage and options for the mysql_upgrade program, see the MySQL Reference Manual: http://dev.mysql.com/doc/mysql/en/mysql-upgrade.html.




Employing Multiple Servers
Run multiple servers when you want to test a new release of MySQL on the same machine where the production server is running. Give each group its own designated root user who is not able to see databases that belong to other groups, as would be possible if all clients were to share the same server.
Do not allow any of the servers to share resources that must be used exclusively by a single server.
The mysqld_multi script is designed to manage several mysqld processes that listen for connections on different UNIX socket files and TCP/IP ports. It searches for groups named [mysqldN] in my.cnf, and it applies the settings for that N to the numbered instance.
For example, to start two mysqld instances that apply settings from my.cnf sections [mysqld3] and [mysqld5] respectively, run the following command:
shell> mysqld_multi start 3, 5


Multiple Server Options
Invoke each MySQL server with mysqld or mysqld_multi and the corresponding options for each server function:
o Data Directory: Starts each server with a unique value for the --datadir option
o Network: Sets servers to use their own network interfaces by starting each server with a unique value for the --port, --socket, and --shared-memory-basename options
o Group Name: Each server group must have a unique mysqldN name on Linux or UNIX, when using mysqld_multi.
o Log Files: Each server must have its own log and PID files. Specify log and PID files with the --log and –-pid-file options.
o InnoDB Tablespace and Log Files: Cannot be shared by multiple servers
o Temporary Directory: Having different temporary directories makes it obvious which server created a particular temporary file. Specify temporary directories with the –-tmpdir option.
o Windows Service Name: Each mysqld Windows service must use a unique service name. Set the service names by using --install. When the servers start, they read options from the corresponding individual service groups in the standard option files.
Another approach is to specify the base directory for each installation with the –-basedir option. This causes each instance to automatically use a different data directory and its own log and PID files because the default for each of those parameters is relative to the base directory.