One 、mysql structure

1. example

1. What is a single instance
A process  +  Multiple threads  +  A pre allocated memory space
2. Multiple instances
Multiple processes  +  Multiple threads  +  Multiple pre allocated memory spaces 


2.mysql Program structure

1) adjoining course

1. Verify user identity
2. There are two ways to connect
3. And sql Layer building thread 

2)sql layer

1. Received from the connection layer sql sentence
2. Judge sql The grammar of
3. Judge sql The semantics of the
4. Parser : analysis sql sentence , Generate execution plan
5. Optimizer : Choose the best execution plan
6. actuator : Carry out the chosen plan ,SQL
6.1  Interact with the storage engine layer , take sql Statement to the storage engine layer
6.2  Display the data from the storage engine layer in the form of a table
7. Write data to cache
8. Log 

3) Storage engine layer

1. And sql Layer to establish interaction , receive sql It's coming from all over the world sql sentence
2. Establish interaction with disk , get data 

3.mysql Logical structure

1. library : The bottom of the library is full of tables
2. surface : Metadata  +  Real data lines
3. Metadata : Column  +  Other attributes ( Row number + Occupied space size + jurisdiction )
4. Column : Name  +  data type  +  Other constraints ( Non empty 、 only 、 Primary key 、 Nonnegative number 、 Self growth 、 The default value is )

4.mysql Physical structure

1.MySQL The bottom layer of the physical structure is the data file , in other words , Storage engine layer , Documents to deal with , It's a data file .
2. There are many kinds of storage engines (Linux Medium FS)
3. The difference between different storage engines : storage 、 Security 、 performance
# Database creation , No matter the database name or the table name , Try to use lowercase letters as much as possible 

5.mysql Physical size

1. paragraph : In theory, a table is a segment , It's made up of multiple zones ,( A partition table is a partition and a segment )
2. District : A continuous number of pages
3. page : The smallest data storage unit , The default is 16k
4. Partition table : One area constitutes one section , It's just a watch 

Two 、mysql Many examples of

#mysql  Multiple instances , Also multiple configuration files start the database
mysql Multiple profiles :
1. Multiple ports
2. Multiple socket file
3. Multiple log files
4. Multiple srver_id
5. Multiple data directories 

1. Create a multi instance Directory

When creating multiple instances, you just need to , Appoint basedir It's just the most original installation path , You can create a multi instance directory in any folder , Pay attention to the impact of permissions on database startup .

[[email protected] ~]# mkdir /service/{3307,3308,3309} -p

2. Edit profile

[[email protected] ~]# vim /service/3307/my.cnf
basedir = /service/mysql
datadir = /service/3307/data
[[email protected] ~]# vim /service/3308/my.cnf 
basedir = /service/mysql
datadir = /service/3308/data
[[email protected] ~]# vim /service/3309/my.cnf 
basedir = /service/mysql
datadir = /service/3309/data

3. Initial knowledge of multi instance data directory

[[email protected] scripts]# ./mysql_install_db --defaults-file=/service/3307/my.cnf --user=mysql --basedir=/service/mysql --datadir=/service/3307/data
[[email protected] scripts]# ./mysql_install_db --defaults-file=/service/3308/my.cnf --user=mysql --basedir=/service/mysql --datadir=/service/3308/data
[[email protected] scripts]# ./mysql_install_db  .cnf --user=mysql --basedir=/service/mysql --datadir=/service/3309/data

4. Authorized Directory

[[email protected] service]# chown -R mysql.mysql /service/

5. Start multiple instances

Note that more memory will be consumed during the startup of multiple instances .

[[email protected] service]# mysqld_safe --defaults-file=/service/3309/my.cnf &
[[email protected] service]# mysqld_safe --defaults-file=/service/3308/my.cnf &
[[email protected] service]# mysqld_safe --defaults-file=/service/3307/my.cnf &

6. Connect multiple instances and verify

[[email protected] service]# mysql -S /service/3308/mysql.sock -e "show variables like 'server_id'"
| Variable_name | Value |
| server_id     | 2     |
[[email protected] service]# mysql -S /service/3309/mysql.sock -e "show variables like 'server_id'"
| Variable_name | Value |
| server_id     | 3     |
[[email protected] service]# mysql -S /service/3307/mysql.sock -e "show variables like 'server_id'"
| Variable_name | Value |
| server_id     | 1     |

7.mysql Multiple instance setting password

[[email protected] service]# mysqladmin -S /service/3307/mysql.sock -uroot password '123'
Warning: Using a password on the command line interface can be insecure.
[[email protected] service]# mysqladmin -S /service/3308/mysql.sock -uroot password '123'
Warning: Using a password on the command line interface can be insecure.
[[email protected] service]# mysqladmin -S /service/3309/mysql.sock -uroot password '123'
Warning: Using a password on the command line interface can be insecure.

8. Set the password and connect

Use tcp/ip The connection of will cause a reverse parsing problem . Skip reverse parsing can be set in the configuration file , Or use it directly socket The way to connect . If you use localhost To connect will report an error, because he will go to the default socket The location of the file ,mysql Multiple instance configuration file socket The file is our own designated location, there will be a situation can not be found .

[[email protected] service]# mysql -S /service/3307/mysql.sock -uroot -p
Enter password:
#  Simple connection
[[email protected] service]# cat /usr/bin/mysql3307
mysql -S /service/3307/mysql.sock -uroot -p123
[[email protected] service]# cat /usr/bin/mysql3308
mysql -S /service/3308/mysql.sock -uroot -p123
[[email protected] service]# cat /usr/bin/mysql3309
mysql -S /service/3309/mysql.sock -uroot -p123
[[email protected] service]# chmod +x /usr/bin/mysql33*

3、 ... and 、 Database multi instance master-slave replication

1. Main warehouse operation

1. Turn on binlog
[[email protected] service]# vim /service/3307/my.cnf 
basedir = /service/mysql
datadir = /service/3307/data
2. The main library authorizes a user , Give it to the slave library to connect
mysql> grant replication slave on *.* to [email protected]'172.16.1.%' identified by '123';
Query OK, 0 rows affected (0.18 sec)
3. Main library view binlog Information
mysql> show master status;
| File             | Position |
| mysql-bin.000004 |      120 |
1 row in set (0.00 sec)

2. Operation of slave Library

# The master database information that the slave database needs to know
The main library IP
The user and password to connect to the main library
binlog Information 
1. Configure master and slave
change master to
1. Turn on IO Threads and sql Threads
mysql> start slave;
Query OK, 0 rows affected (0.08 sec)

3.IO Thread is not for YES

IO The main function of thread is to pull bin_log The operation , Then execute on your own database to achieve the purpose of synchronization .

1. Test network
[[email protected] ~]# ping
2. Test port
[[email protected] ~]# telnet 3307
3. Test the login of the user authorized by the main library
[[email protected] ~]# mysql -urep -p123 -h172.16.1.53 -P3307
4. Reverse DNS
[[email protected] service]# vim /service/3307/my.cnf 
5.UUID identical
When cloning ,uuid The same will lead to the master-slave replication problem of the configuration database .
[[email protected] ~]# cat /service/3307/data/auto.cnf 

4.SQL Thread is not for YES

Created a database on the slave Library , It can lead to this problem .

1. The main database has data , No from the library
2. There's data from the library , There is no such thing as
#  The master-slave database data is inconsistent