MySQL installation and configuration

MySQL installation

Binary package installation

Mkdir -p /beicen/db/mysql/data/{data,log, TMP} groupadd mysql useradd -r -g mysql -s /bin/false mysql ln -s /beicen/db/mysql/mysql-5.7.34-el7-x86_64/ /usr/local/mysql chown -r mysql:mysql /usr/local/mysql/  chown -R mysql:mysql /beicen/db/mysql vim /beicen/db/mysql/my.conf /usr/local/mysql/bin/mysqld --defaults-file=/beicen/db/mysql/my.conf --initialize --user='mysql' --log_error_verbosity --explicit_defaults_for_timestamp mysqld_safe --defaults-file=/beicen/db/mysql/my.conf &Copy the code

RPM Package Installation (Redhat)

# # download the installation package can choose https://repo.mysql.com/yum/ wget download version https://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/mysql-community-libs-5.7.31-1.el7.x86_64.rpm wget https://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/mysql-community-common-5.7.31-1.el7.x86_64.rpm wget https://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/mysql-community-client-5.7.31-1.el7.x86_64.rpm wget https://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/mysql-community-server-5.7.31-1.el7.x86_64.rpm # RPM -uvh --nodeps RPM -uvh --nodeps RPM -uvh --nodeps RPM -uvh --nodeps RPM -uvh --nodeps RPM -uvh --nodeps RPM -uvh --nodeps RPM -uvh --nodeps RPM -uvh --nodeps RPM -uvh --nodeps RPM -uvh --nodeps RPM RPM -uvh --nodeps mysql-community-client-5.7.31-1.el7.x86_64. RPM RPM -uvh -- nodeps mysql - community - server - 5.7.31-1. El7. X86_64. RPMCopy the code

Initial Configuration

Data directory Modification

The default directory is /var/lib/mysql. The default directory is /var/lib/mysql. Mysql /data/ DBS /mysql57/data_local mysql /data/ DBS /mysql57/data_local Mysql57/data_local /var/lib/mysql/* /data/ DBS /mysql57/data_local systemctl start mysqld.service mysql -uroot -p > show variables like '%data%'; View the specified variableCopy the code

Account Permission Management

Grep 'temporary password' /var/log/mysqld.log #2 Mysql -u root -p' temporary password '#3 Change the root@localhost password(password rule: small and large letters + lowercase letters + Special characters + digits) set password for 'root'@'localhost' = password('XXXX'); Create user 'root'@'%' identified by 'XXXX '; Grant all on *.* to 'root'@'%'; #6. Refresh PRIVILEGES;Copy the code

Parameter configuration and optimization

[mysql] default-character-set = utf8 [mysqld] sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES Performance_schema_max_table_instances =800 table_definition_cache=800 table_open_cache=512 # Server encoding Character_set_server = UTf8 # case-insensitive lower_case_table_names=1 # Max_connections =1024 key_BUFFer_size =512M innodb_buffer_pool_size=512MCopy the code

Basic Database Operations

Create database XXX character set UTf8MB4; Show databases/tables desc tableCopy the code

Data backup and initialization

The data backup

  • mysqldump
Mysqldump -h 127.0.0.1 -u root -p database name > file name.sqlCopy the code

Data reduction

  • source

Note: If the file size is too large, modify related parameters to improve restoration efficiency

Set global MAX_allowed_packet =100000000; Set global net_buffer_length=100000; Set global interactive_timeout=28800000; Set global wait_timeout=28800000; Mysql -uroot -p mysql -uroot -p mysql Use source source XXX /xxx.sqlCopy the code

FAQ Resolution

Can’t start server: Bind on TCP/IP port: Permission denied

Vim /etc/selinux/config selinux =disabled #2 Temporarily modify setenForce 0Copy the code