Shulou(Shulou.com)06/01 Report--

The version of the database

1. Community version

two。 Enterprise edition

3. Cluster version

Installation of database

1. Dedicated package Manager (binary)

Deb, rpm, etc.

Mysql MySQL client programs and shared libraries

Related programs required by mysql-server MySQL server

two。 Source code package (compilation and installation)

Configure 、 cmake

Common configuration options for databases

-DCMAKE_INSTALL_PREFIX=/usr/local/mysql-specify the stump installable path (default is / usr/local/mysql)

-DMYSQL_DATADIR=/data/mysql-the path to the data file of mysql

-DSYSCONFDIR=/etc-configuration file path

-DWITH_INNOBASE_STORAGE_ENGINE=1-using the INNOBASE storage engine

-DWITH_ARCHIVE_STORAGE_ENGINE=1-often used for logging and aggregation analysis, but does not support indexing

-DWITH_BLACKHOLE_STORAGE_ENGINE=1-Black Hole Storage engine

-introduction to canceling some storage engine instructions during DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 compilation

-DWITHOUT_FEDERATED_STORAGE_ENGINE=1

-DWITHOUT_PARTITION_STORAGE_ENGINE=1

-DWITH_READLINE=1-support batch import of mysql data

-DWITH_SSL=system-mysql supports ssl sessions and implements data recovery based on ssl

-DWITH_ZLIB=system-Compression Library

-DWITH_LIBWRAP=0-whether access control can be implemented based on WRAP

-DMYSQL_TCP_PORT=3306-default port

-DMYSQL_UNIX_ADDR=/tmp/mysql.sock-default socket file path

-DENABLED_LOCAL_INFILE=1-whether to enable the LOCAL_INFILE feature

-DEXTRA_CHARSETS=all-whether additional character sets are supported

-DDEFAULT_CHARSET=utf8-default coding mechanism

-DDEFAULT_COLLATION=utf8_general_ci-sets the collation of the default language

-DWITH_DEBUG=0-DEBUG function setting

-DENABLE_PROFILING=1-whether the performance analysis feature is enabled

3. Service: mysqld

4. Port: 3306

5. Main configuration file: / etc/my.cnf

6. Script: mysql_install_db

7. Mysqld_safe

8. Data directory: / var/lib/mysql

9. Socket file: / var/lib/mysql/mysql.sock

10. When you accidentally close the database, if you can't open it again, find this, delete it and start it again.

11. Process file: / var/run/mysqld/mysqld.pid

Log in and out of the mysql environment

A) set the password mysqladmin-uroot password '123'

B) Log in to mysql-u user name-p

-p user password

-h login location (hostname or ip address)

-P port number (not if 3306 is changed)

-S socket file (/ var/lib/mysql/mysql.sock)

C) exit exit

D) create login user create user username @'% 'identified by' password'

E) change password set password=password ('new password')

Setpassword for user @ login location = password ('new password')-root user retrieves passwords for other users

When the administrator forgets his password, how to get it back?

1) close the database

2) modify the main configuration file (/ etc/my.cnf) / backup path / backup file name (backup a single database)

Mysqldump-u user name-p database name table name > / backup path / backup file name (backup data table)

-- databases Library 1, Library 2 (restore at this time-> mysql

< 备份文件) --all-databases-备份服务器中的所有数据库内容 还原：mysql 数据库 < 备份文件 mysqlhotcopy 备份：mysqlhotcopy--flushlog -u='用户' -p='密码'--regexp=正则 备份目录 还原：cp-rpf 备份目录 数据目录（/var/lib/mysql） 补充的备份机制 1.日志备份 >

Mysqlshow global variables like'% log%'

List the log-related variables in mysql

Error log

Information when the server starts and shuts down

Error message during server operation

Information generated when starting a process from the server from the server

The path to the log-error error log

General logging (not enabled)

Record the user's query operations to the database

General-log=ON launches the general query log

Log=ON Global Log switch

Record type of log-output log

Slow query log

Recording takes a long time to query

Log-slow-queries= saves the path to start the slow query log and sets a path

Binary log

All actions to change the state of the database (create, drop, update, etc.)

Log-bin= location startup binary log

Mysql show binary logs to view the currently used binary log

> mysqlshow binlog events in 'binary log (mysql-bin.000001)' view the contents of the binary log

Restore: (mysqlbinlog)

Restore by time:

Mysqlbinlog-- start-datetime 'YY-MM-DD HH:MM:SS'--stop-datetime' YY-MM-DD HH:MM:SS' binary log | mysql (- urot-p)

Restore by file size:

-- start-position

-- stop-position

Transaction logs: recording transaction-related log information

Relay log: recording backup information from the server

two。 Multi-machine backup

Master-slave configuration: real-time backup

Master and master configuration: (master and slave configured twice) real-time backup, load balancing

Multi-slave master: real-time backup (more backup nodes)

Multi-master and one-slave: real-time backup, cost saving

Experiment

1. The login user yzh who created the mysql can log in to the mysql server

1. Create a login user

# yum install mysql-server-y

# servicemysqld start

Test on another virtual machine

The created user changes the password for himself

Root users retrieve passwords for other users

Root retrieves his password and modifies it

The experiment of adding, deleting, changing and checking the database

Create a database

Create a datasheet

A little more complicated.

Insert data

Copy the data from table a2 to table A1

Delete database

Delete data tabl

Delete the data record in the table

Delete those between the ages of 23 and 25

Modify the data in the table

Modify the name of the data table

Modify the field type of the data table

Modify the fields of the data table

Add a field

Delete a field

Authorize the user

1. Give the user full permissions

Revoke the permission of yzh users to delete data from libraries, tables, and tables

View the user's permissions

two。 Backup and restore database files

Single backup command mysqldump-uroot-p-- databases ab > / ab.sql

Backup of multiple databases separated by spaces

Mysqldump-uroot-p-- databases aa ab > / 1.sql

1. Backup the database aa to the / root directory

two。 Simulation database aa lost (delete database aa)

3. Reduction

Note: errors may be reported here, as follows:

The solution is:

A. Modify the configuration file and add skip-grant-tables to skip password verification. Change the root password after entering mysql

B. Delete the skip-grant-tables in the configuration file, restart the service and re-enter with a new password!

1. Back up multiple databases (--databases)

two。 Reduction

1. Back up a database with rules

two。 Simulated database deletion

3. Reduction

5. About binary log restore (none of the experiments were successful here, try again later)

Open binary log

View binary log files

Restore by time:

1. If the bb library in the database is deleted, it needs to be restored

two。 View the contents of the binary log

3. Restore and view

Restore by file size:

Restore to the deleted data state of the bb library

1. Check the file size before and after the deletion of the bb library

two。 Restore and view

6. Master-slave backup

Prerequisites: mysql installed, binary logging enabled

License on the master server and save the authorization information from the server

After that, the authorization information file is generated from the slave server.

Enable start slave from the slave server and view

test

7. Master master backup (directly reconfigure the master-slave reverse of experiment 6, you can also do master-master backup). The parameters added in this experiment are only to improve efficiency, and the effect is significant in the case of large database data.

1. Take 1 as the main, 2 as the slave configuration once the master and slave

A) configure it in the main configuration file (open binary log and other contents)

B) do the same configuration on 2

C) start the server

D) Authorization on the primary server (1)

E) Save authorization information on the slave server (2)

two。 Take 2 as the main, 1 as the slave configuration once master-slave

A) Authorization on the primary server (2)

B) Save authorization information on the slave server (1)

3. Enable start slave from the slave server and view

4. test

8. Follow more than one master

1. Master server configuration

A) Open binary log and enable mysql

B) Authorization on the primary server

two。 Configure from the server

A) Open the binary log and start mysql

B) Save authorization information

C) do the same configuration on another slave server

D) testing

9. Multi-master and one slave

1. Master server configuration

A) start the binary log and start the service

B) authorization

C) do the same on primary server 2

two。 Operate from the server

A) operate on the main profile

B) initialize the database and generate the directory mysqla,mysqlb

C) set the permissions of the owners of the mysqla,mysqlb directory and the following files to mysql (to prevent permission problems)

D) start the thread from the server

E) log in and save the authorization letter

3. test

10. Separation of reading and writing

Environment deployment:

Master server-192.168.115.191

Slave server-192.168.115.193

Middleware server-192.168.115.192

Middleware: a program that provides the sharing of resources between different technologies and different software

Tomcat weblogic

Database middleware: mysql proxy (official version) has low performance and requires lua scripts

Atlas has low performance and long corresponding time.

Developed by amoeba Chen Silu.

One. First set up a master-slave server

1. Install mysql mysql-server on the master and slave servers

two。 Open binary log

3. Authorize on the master server, save the authorization information on the slave server, and start the slave service thread.

Master server

Slave server

Two. configure read-write separation (configured on 192.168.115.192 middleware server)

1. Install the gcc environment (amoeba requires source code installation)

two。 Copy third-party software to create a separate directory

3. Install jdk first (amoeba is written in the java language, so install jdk first) and configure the java environment

Declare how a program written in java can be called (/ etc/profile) to add content manually

4. Install amoeba

A) decompression

B) configure the software amoeba

. Region

. Region

Since only one server template is provided, you need to copy another one for reading (directly copy 64 lines to 109 lines, insert after 109 lines)

Modify the read-write server at the lower end

C) start amoeba

Modify the startup script vim / usr/local/amoeba/bin/amoeba

First turn on the authorization of the amoeba user on the master server 191 (automatic synchronization from the server)

Then open the amoeba service on the 193th amoeba server

Nohup bash-x / usr/local/amoeba/bin/amoeba& put this in the background and exit the terminal, or you can continue to run.

Ps aux | grep amoeba and then check the running program. If you see it, it means the program is already running.

III. Test (install a MySQL package to connect)

Start the mysql service service mysqld start on Amoeba

Log in to the mysql service on the Amoeba side using the user name and password in the amoeba configuration in the configuration file

Disable the slave synchronization function of the slave server

Create table A1 on the master and slave servers, and insert data into the tables of the master server

Then test the login on the client:

Effect of reading pool:

Write pool effect

The above tests are purely for experimental results. In actual production, when the master and slave are turned on, the data written on the master server will be synchronized to the slave server.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.