未加星标

MySQL 5.7 and administration

字体大小 | |
[数据库(mysql) 所属分类 数据库(mysql) | 发布者 店小二05 | 时间 2016 | 作者 红领巾 ] 0人收藏点击收藏

mysql 5.7 improved as compare to previous releases in terms of transnational capabilities, performance with high load, high Availability, Security and it’s defaults.

Check my blog post :MySQL 5.7 features

This blog post will describe End to End implementation of MySQL on linux distributions Which will cover MySQL Installation, configuration and administration in production environment with proper configuration. So you can start using your application by implementingfollowing setup and in future you can change it if requires.

Database Installation MySQL Variables and Configuration MySQL User Management MySQL DB Backup and Restore Database Installation:

There are number of ways to install MySQL,

Source code Binaries : Compressed tar file binary distribution Packages : RPM-based Linux distributions MySQL Installer MSI and ZIP Archive Yum repository

MySQL installation using packages is one the easy way to install MySQL and you don’t have to worry about installation configuration part.Another option is installing MySQL using compressed tar file, with this installation method user has to performinstallation and most of the configuration part.

On windows :MySQL Installer MSI will take care of everything including installation of supporting monitoring tool and utilities, MySQL configuration settings and user management

Let’s Install MySQL RPM packages.For standard installation, we will install mysql-community-server , mysql-community-client , mysql-community-libs , mysql-community-common , and mysql-community-libs-compat packages.

Always use new version of MySQL GA release for new installations.

MySQL Installation steps:

1. Download RPM package from for your OS architecture : http://dev.mysql.com/downloads/mysql

2. Run following command to install MySQL.

sudo yum install mysql-community-{server,client,common,libs}-*

The installation also creates a user named mysql and a group named mysql on the system.

3. Default configuration file location /etc/my.cnf file.

4. Standard directory structure for MySQL:

A standard installation of MySQL using the RPM packages result in files and resources created under the system directories, shown in the following table.


MySQL 5.7 and administration

MySQL basedir will have default distributed dir structure,except datadir. It is recommended to keep datadir in adifferent location for numerous reasons.For,this you just need to update ‘datadir’ variable value with new datadir location

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

By,default my.cnf will get created by MySQL rpm installation with default configuration in it. We needto add few more configuration variables in order to make MySQL DB server ready for production use. Following are the standard configuration settings for a production database. These variable values may vary as per the application scope and data workload.

MySQL configuration file sections MySQL configuration file have many sessions, such as [mysql], [client], [mysqld_safe] and so on. Add/update following variables to appropriate configuration section of my.cnf. [mysqld] #GENERAL user = mysql port = 3306 server_id=152 skip-name-resolve default_storage_engine = InnoDB socket = /var/lib/mysql/mysql.sock pid_file = /var/run/mysql/mysqld.pid # DATA STORAGE datadir =/var/lib/mysql/data #INNODB innodb_file_per_table=1 innodb_buffer_pool_size = 4000M (60-70 % of RAM memory) innodb_data_file_path= ibdata1:1G;ibdata2:500M:autoextend innodb-log-files-in-group=3 innodb_flush_method = O_DIRECT #Logging log_error = /var/log/mysql/mysqld.log master_info_repository = TABLE relay_log_info_repository = TABLE log-bin=mysql-bin152 relay-log=relay-bin152 relay_log_recovery=on log-slave-updates= 1 expire_logs_days = 7 gtid-mode=on enforce-gtid-consistency=1 binlog_format=row [mysql] socket = /var/lib/mysql/mysql.sock [client] socket = /var/lib/mysql/mysql.sock port = 3306

6.Start MySQL service

sudo service mysqld start

7. At the initial start-up of the server, the following happens, when MySQL data directory is empty:

The server is initialized. An SSL certificate and key files are generated in the data directory. The validate_password plugin is installed and enabled. A superuser account’root’@’localhost’is created. A password for the superuser is set and stored in the error log file. Look for root password in error log file. sudo grep 'temporary password' /var/log/mysqld.log

8. After login first time into MySQL, we can notproceed further without resetting root password.

shell> mysql uroot -p mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'r00t$PWD';

For any startup issue check /var/log/mysqld.log

MySQL Variables and Configuration: MySQL Variables User mysql service user Server-id

Any number in DB group

Port Value: 3306 default Skip-name-resolve Do not resolve host names when checking client connections. Use only IP addresses. bind_address

IPv4 : 0.0.0.0

IPv4 and IPv6 : *

Socket Unix socket file for listening local connections Pid-file The path name of the process ID file. default_storage_engine

Value: Innodb

Datadir Main directory where database,system tablespace and log files will get store. innodb_file_per_table

Value : on

Innodb_buffer_pool_size Value should be 60-70 percent of RAM memory of server innodb_log_file_size

Value should be greater if you are using BLOB datatype in your database.

Value: innodb_log_file_size=150M

innodb_log_files_in_group

Value : 3

innodb_data_file_path=

Value:ibdata1:1G;ibdata2:1G:autoextend (vary)

innodb_flush_method

value : O_DIRECT

innodb_tmpdir tmp directory for ONLINE ALTER operations. log_error mysql server log log-bin Binary log file name

Value : mysql-bin152

binlog_format binary log formate for data

Value: row

relay_log_info_repository

crash-safe replication settings, storing log info in table instead of file.

Value: TABLE

relay-log

relay-log=relay-bin152

relay_log_recovery

本文数据库(mysql)相关术语:navicat for mysql mysql workbench mysql数据库 mysql 存储过程 mysql安装图解 mysql教程 mysql 管理工具

主题: MySQLSQLLinuxInnoDBWindowsTIIPv4IPv6
分页:12
转载请注明
本文标题:MySQL 5.7 and administration
本站链接:http://www.codesec.net/view/480208.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mysql) | 评论(0) | 阅读(45)