YouTipMysql Administration
# MySQL Administration
## Starting and Stopping the MySQL Server
### On Windows
**Starting the MySQL Server:**
1. **Via the "Services" management tool:** Open the "Run" dialog (Win + R), type **services.msc**, find the "MySQL" service, right-click and select "Start".
**2. Via Command Prompt:** Open Command Prompt (as administrator) and enter the following command:
net start mysql
**Stopping the MySQL Server:**
1. **Via the "Services" management tool:** Similarly, open the "Run" dialog, type services.msc, find the "MySQL" service, right-click and select "Stop".
2. **Via Command Prompt:** Open Command Prompt (as administrator) and enter the following command:
net stop mysql
### On Linux
**1. Starting the MySQL Service:**
Using the systemd command (applicable to most modern Linux distributions like Ubuntu, CentOS, etc.):
sudo systemctl start mysql
Using the service command (on some older distributions):
sudo service mysql start
**2. Stopping the MySQL Service:**
**Using systemd:**
sudo systemctl stop mysql
Using the service command:
sudo service mysql stop
**3. Restarting the MySQL Service:**
**Using systemd:**
sudo systemctl restart mysql
Using the service command:
sudo service mysql restart
**4. Checking MySQL Service Status:**
Using the systemd command:
sudo systemctl status mysql
Using the service command:
sudo service mysql status
### On Mac OS
Starting the MySQL Service:
Using the command line:
sudo /usr/local/mysql/support-files/mysql.server start
Stopping the MySQL Service:
Using the command line:
sudo /usr/local/mysql/support-files/mysql.server stop
Restarting the MySQL Service:
Using the command line:
sudo /usr/local/mysql/support-files/mysql.server restart
Checking MySQL Service Status:
Using the command line:
sudo /usr/local/mysql/support-files/mysql.server status
In the above commands, `mysql` might vary depending on the installation path or version.
On Mac OS, the MySQL installation path is typically `/usr/local/mysql/`, so starting and stopping the MySQL service requires using the `mysql.server` script from this path.
* * *
## MySQL User Setup
In MySQL, user setup includes creating users, setting permissions, managing users, etc. Below are some common MySQL user setup operations, including creating users, setting permissions, viewing and deleting users, etc.
### Creating a User
To create a new user, you can use the following SQL command:
CREATE USER 'username'@'host' IDENTIFIED BY 'password';
* `username`: The username.
* `host`: Specifies from which hosts the user can connect. For example, `localhost` allows only local connections, `%` allows connections from any host.
* `password`: The user's password.
## Example
CREATE USER'john'@'localhost'IDENTIFIED BY'password123';
### Granting Privileges
After creating a user, you need to grant them access privileges using the GRANT command:
GRANT privileges ON database_name.* TO 'username'@'host';
* `privileges`: The required privileges, such as `ALL PRIVILEGES`, `SELECT`, `INSERT`, `UPDATE`, `DELETE`, etc.
* `database_name.*`: Indicates granting privileges on a specific database or table. `database_name.*` grants privileges on all tables in the database, `database_name.table_name` grants privileges on a specified table.
* `TO 'username'@'host'`: Specifies the user and host to grant privileges to.
## Example
GRANT ALL PRIVILEGES ON test_db.*TO'john'@'localhost';
### Flushing Privileges
After granting or revoking privileges, you need to flush privileges for the changes to take effect:
FLUSH PRIVILEGES;
### Viewing User Privileges
To view the privileges of a specific user, you can use the following command:
SHOW GRANTS FOR 'username'@'host';
## Example
SHOW GRANTS FOR'john'@'localhost';
### Revoking Privileges
To revoke a user's privileges, use the REVOKE command:
REVOKE privileges ON database_name.* FROM 'username'@'host';
## Example
REVOKE ALL PRIVILEGES ON test_db.*FROM'john'@'localhost';
### Deleting a User
If you need to delete a user, you can use the following command:
DROP USER 'username'@'host';
## Example
DROP USER'john'@'localhost';
### Changing a User's Password
To change a user's password, you can use the ALTER USER command:
ALTER USER 'username'@'host' IDENTIFIED BY 'new_password';
## Example
ALTER USER'john'@'localhost'IDENTIFIED BY'newpassword456';
### Changing a User's Host
To change a user's host (i.e., from which hosts they can connect), you can first delete the user and then recreate them with a new host.
## Example
-- Delete the old user
DROP USER'john'@'localhost';
-- Recreate the user with a new host
CREATE USER'john'@'%'IDENTIFIED BY'password123';
### Specifying Privileges When Creating a User
When creating a user, you can also grant privileges simultaneously (in MySQL 8.0.16 and later):
## Example
CREATE USER'john'@'localhost'IDENTIFIED BY'password123'WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON test_db.*TO'john'@'localhost';
* * *
## /etc/my.cnf File Configuration
The `/etc/my.cnf` file is the MySQL configuration file, used to configure various parameters and options for the MySQL server.
Generally, you do not need to modify this configuration file. The default configuration is as follows:
datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock [mysql.server] user=mysql basedir=/var/lib err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid
In the configuration file, you can specify the directory for different error log files. Generally, you do not need to change these settings.
The `/etc/my.cnf` file may vary across different systems and MySQL versions, but it generally contains the following sections:
### 1. Basic Settings
* `basedir`: The base installation directory for the MySQL server.
* `datadir`: The location where MySQL data files are stored.
* `socket`: The path to the MySQL server's Unix socket file.
* `pid-file`: The path to the file storing the process ID of the currently running MySQL server.
* `port`: The port number the MySQL server listens on, default is 3306.
### 2. Server Options
* `bind-address`: Specifies the IP address the MySQL server listens on, can be an IP address or hostname.
* `server-id`: In replication configurations, sets a unique identifier for each MySQL server.
* `default-storage-engine`: The default storage engine, e.g., InnoDB or MyISAM.
* `max_connections`: The maximum number of connections the server can maintain simultaneously.
* `thread_cache_size`: The size of the thread cache, used to improve the startup speed of new connections.
* `query_cache_size`: The size of the query cache, used to improve the efficiency of identical queries.
* `default-character-set`: The default character set.
* `collation-server`: The server's default collation.
### 3. Performance Tuning
* `innodb_buffer_pool_size`: The size of the InnoDB storage engine's buffer pool, one of the most important parameters for InnoDB performance tuning.
* `key_buffer_size`: The size of the MyISAM storage engine's key buffer.
* `table_open_cache`: The number of table caches that can be open simultaneously.
* `thread_concurrency`: The number of threads allowed to run simultaneously.
### 4. Security Settings
* `skip-networking`: Disables the MySQL server from listening for network connections, allowing only local connections.
* `skip-grant-tables`: Starts the MySQL server without requiring passwords, typically used for recovering a forgotten root password, but this is a security risk.
* `auth_native_password=1`: Enables native password authentication for MySQL 5.7 and above.
### 5. Log Settings
* `log_error`: The path to the error log file.
* `general_log`: Logs all client connections and queries.
* `slow_query_log`: Logs slow queries that exceed a specific execution time threshold.
* `log_queries_not_using_indexes`: Logs queries that do not use indexes.
### 6. Replication Settings
* `master_host` and `master_user`: The address and replication user of the master server.
* `master_password`: The password for the replication user.
* `master_log_file` and `master_log_pos`: The log file and position used for replication.
* * *
## Commands for Managing MySQL
The following lists common commands used in the process of working with a MySQL database:
* **USE _database_name_**:
Selects the MySQL database to operate on. After using this command, all subsequent MySQL commands apply only to that database.
mysql> use ;Database changed
* **SHOW DATABASES:**
Lists the databases in the MySQL database management system.
mysql> SHOW DATABASES;+--------------------+| Database |+--------------------+| information_schema || || cdcol || mysql || onethink || performance_schema || phpmyadmin || test || wecenter || wordpress |+--------------------+10 rows in set (0.02 sec)
* **SHOW TABLES:**
Displays all tables in the specified database. Before using this command, you need to use the `use` command to select the database to operate on.
mysql> use ;Database changed mysql> SHOW TABLES;+------------------+| Tables_in_tutorial |+------------------+| employee_tbl || tutorial_tbl || tcount_tbl |+------------------+3 rows in set (0.00 sec)
* **SHOW COLUMNS FROM _table_name_:**
Displays the properties, attribute types, primary key information, whether it can be NULL, default values, and other information of a data table.
mysql> SHOW COLUMNS FROM tutorial_tbl;+-----------------+--------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-----------------+--------------+------+-----+---------+-------+| tutorial_id | int(11) | NO | PRI | NULL | || tutorial_title | varchar(255) | YES | | NULL | || tutorial_author | varchar(255) | YES | | NULL | || submission_date | date | YES | | NULL | |+-----------------+--------------+------+-----+---------+-------+4 rows in set (0.01 sec)
* **SHOW INDEX FROM _table_name_:**
Displays detailed index information for a data table, including PRIMARY KEY.
mysql> SHOW INDEX FROM tutorial_tbl;+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| tutorial_tbl | 0 | PRIMARY | 1 | tutorial_id | A | 2 | NULL | NULL | | BTREE | | |+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+1 row in set (0.00 sec)
* **SHOW TABLE STATUS [LIKE 'pattern'] G:**
This command outputs performance and statistical information about the MySQL database management system.
mysql> SHOW TABLE STATUS FROM ; # Displays information for all tables in the database mysql> SHOW TABLE STATUS from LIKE '%'; # Information for tables whose names start with '' mysql> SHOW TABLE STATUS from LIKE '%'G; # Adding G prints the query results in column format
Gif Demo:
!(#)