Mysql client and server model

Mysql service structure

Mysql is a typical C/S mode, consisting of server and client. Server program mysqld client program mysql has its own client (mysql, mysqladmin, mysqldump, etc.) third-party client API interface (PHP-mysql)

Mysql connection mode

TCP/IP connection Network connection string (connect by user name password IP port mysql -uroot -p123 -h 127.0.0.1 -p 3306

Socket connection network socket (user name password socket file) mysql – uroot – p123 -s/application/mysql/TMP/mysql. The sock

In Linux, run the mysql command without other parameters. The connection mode is mysql-uroot-poldboy123. The login mode is socket file

In Linux, run the mysql command without other parameters. The connection mode is mysql-uroot-poldboy123. The login mode is socket file

MySQL is being started

Start daemon and generate worker thread pre-allocated memory structure for MySQL to process data. Example: MySQL daemon + thread + pre-allocated memory structure

4. Mysql storage mode

The program files are stored in the server installation directory along with the data directory. Program executables and log files are created when executing various client programs, hypervisors, and utilities. The primary user of disk space is the data directory. Server log files and status files: Contains information about the statements processed by the server. Logs can be used for troubleshooting, monitoring, replication, and recovery. InnoDB log files: (for all databases) reside at the data directory level. InnoDB system tablespace: contains data dictionary, undo log and buffer. Each database has a single directory under the data directory (no matter what type of tables are created in the database). The database directory stores the following: Data files: Data files that are specific to the storage engine. These files may also contain metadata or index information, depending on the storage engine used. Format file (.frm) : Contains a description of each table and/or view structure, located in the corresponding database directory. Trigger: A named database object associated with a table and activated when a specific event occurs in that table. The location of the data directory depends on configuration, operating system, installation package, and distribution. The typical location is /var/lib/mysql. MySQL stores the system database (MySQL) on disk. Mysql contains information such as users, privileges, plugins, help lists, events, time zone implementations, and storage routines. The logical structure library isa directory for storing multiple tables. (.myd datafile. Myi index file FRM table structure definition file) innoDB: two or one, shared table space (IBDATA1 base table metadata), independent table space (default table storage mode after 5.6)

(2) Mysql management

1, Mysql common operations

Connection management mysql –help

-u <user_name> or –host=<user_name> -p -h <host_name> or –host=<host_name> –protocol=<protocol_name> -p <port_number> or –port=<port_number> -s <socket_name> or –socket=<socket_name>

Mysql > use mysql;

Mysql > show tables;

Mysql > select * from user;

Mysql > start/stop mysql

Win： C:\Users\yujf> net start mysql

The MySQL service is starting. The MySQL service is started successfully.

Linux： servive mysqld start

The recommended method is Win: net stop mysql

Linux:

#mysqladmin -uroot -p123 shutdown

#servive mysqld stop

Avoid using the kill command

The mysqladmin command closes mysql

C:\Users\yujf>mysqladmin shutdown -uroot -p

Enter password: ********

Check mysqld status

#mysqladmin status -uroot -p

3. My.cnf configuration file

1. The server process startup is affected

2. Client programs are affected

Configure my CNF

Use different “tags” to explicitly specify which functions are affected

Server side [server]

[mysqld] -----> MysqLD_safeCopy the code

[client] —-> To facilitate setting this item

[mysql] 
[mysqladmin]
[mysqldump]
Copy the code

Example of configuration of my.cnf file

cat /etc/my.cnf

[mysqld]
basedir=/application/mysql
datadir=/application/mysql/data
socket=/tmp/mysql.sock
log-error=/var/log/mysql.log
port=3307
[mysql]
socket=/tmp/mysql.sock
user=root
password=123
Copy the code