Shulou(Shulou.com)05/31 Report--

Editor to share with you how to initialize the MySQL database server, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

Risky operation commands are forced to be implicitly rewritten

1) Shell command rm: no matter what parameters you enter after the rm, it will be implicitly converted to rm-I command execution.

2) Shell command cp: no matter what parameters you enter after the cp, it will be implicitly converted to cp-I command execution.

3) Shell command mv: no matter what parameters you enter after the mv, it will be implicitly converted to mv-I command execution

Modify the contents of the hidden file bash_profile in the default configuration of each operating system-level account to achieve the invisible modification of the above three commands, and may also add other information such as paths and prompts. For specific details, please refer to the article to set the environment variables of the Linux system to reduce the occurrence of tragic operations.

Server name

The host name should contain three parts of information: the name of the computer room where the server is located, the application name, and the code in the cluster. In addition, in order to facilitate unified management, communicate and cooperate with other fraternal departments, and change the practice of their own rules in the past, and formulate strict format requirements, the connection of the three parts conforms to the unified "-", with examples: xy-brmms-db1a, xy-brmms-db1b, xy-brmms- db2a, xy-brmms-db2b … .

Db1a and db1b represent a master-slave replication relationship between master and slave. If there are multiple servers in the cluster that support data services, the numbers and letters of the last two digits will change accordingly.

ITIL process order submission

1) Domain name application: if the application accesses the database server through the domain name rather than the IP address, you need to submit an ITIL flow sheet and be designated as a * colleague in the SA department, which will usually be processed within 2 hours, so you need to prepare the application in advance.

2) change the IP address of the server: if you want to change the IP address of the database server, including the IP address used for VIP, you need to submit an ITIL flow sheet and designate it as a * colleague in the network department. This request will generally be responded to in a timely manner, but it is still recommended to fill in it in advance.

3) increase of server access safe list: after all the configuration work of the server is completed, a clear description is required by submitting the ITIL process to the security department: the IP address information of the server (Note: if you need to use VIP or domain name, you also need to describe clearly), the name of the host, and the members or individuals of that group need to access the server. And clearly describe the permissions they need to have, as well as the name of the account to log in by default (generally default: admin)

MySQL package installation

In view of the fact that we use the community version of MySQL open source software, its testing and verification process mainly depends on community users and open source contributors, even commercial products can not blindly use the latest release. In addition, the version we use must be tested by us and verified by the development environment or performance test environment. Versions 5.1.34, 5.1.36, 5.1.40 are recommended for this purpose. If you want to use Plugin-innodb, it is recommended to use version: mysql-5.1.48, but not for production environment: mysql 5.5.* series.

Taking mysql version 5.1.40 as an example, the installed software packages are:

Double-click the code to select all 1 2 3 4MySQL-server-community-5.1.40-0.rhel5.x86_64.rpm MySQL-shared-community-5.1.40-0.rhel5.x86_64.rpm MySQL-client-community-5.1.40-0.rhel5.x86_64.rpm MySQL-devel-community-5.1.40-0.rhel5.x86_64.rpm

Initialization of MySQL database

1) both active and standby servers give priority to increasing the account for replication

GRANT REPLICATION SLAVE ON *. * TO 'repl'@'%' IDENTIFIED BY'XXXXXXXXXXXXXXXXXX'

2) after the master replication is completed, select a host to perform other initialization operations

Information about initializing the root account:

DELETE FROM mysql.user WHERE user= "OR host="; UPDATE mysql.user SET password=PASSWORD ('XXXXXXXXXXXXXXXX') WHERE user='root'; FLUSH PRIVILEGES

Create database information for monitoring:

CREATE DATABASE `monitor` CHARACTER SET utf8 COLLATE utf8_general_ci; USE monitor; CREATE TABLE `xdual` (`id` int (11) NOT NULL AUTO_INCREMENT, `x` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; INSERT INTO xdual (x) VALUES (NOW ())

Create account information for management, monitoring, and local backups:

GRANT SUPER,PROCESS,FILE,SELECT ON *. * TO 'admin'@'localhost' IDENTIFIED BY'XXXXXXXXXXXXXXXXX'; GRANT UPDATE ON monitor.* TO' admin'@'localhost'

Create an account for read-only application scenarios (for example, data warehouse):

ANT SELECT ON stanley.* TO 'rnd'@'%' IDENTIFIED BY'XXXXXXXXXXXXXXXX'

Add the account information of the corresponding library for the application and DBA operations:

GRANT SELECT,INSERT,UPDATE,DELETE ON stanley.* TO 'stanley'@'%' IDENTIFIED BY'XXXXXXXX'; GRANT ALL ON stanley.* TO' stanley'@'localhost' IDENTIFIED BY'XXXXXXXXXXXXXXXX'

Remarks:

When DBA personnel access the mysql database server to complete specific work, they must try their best to use the account with corresponding permissions instead of the account with the highest permission, root. Although this may lead to an increase in cost, it can reduce the probability of misoperation events and reduce the scope of misoperation.

MySQL replication build

Because the newly installed database server has not run any applications, but both hosts have created a database account information with the same attribute, it is not necessary to perform database backup to build replication, which is briefly described as follows (Note: take xy-brmms-db1a and xy-brmms-db1b as an example):

(1) execute the SQL command on the host xy-brmms-db1a: SHOW MASTER STATUS

(2) execute SQL command on the host xy-brmms-db1b:

CHANGE MASTER TO master_host='172.22.32.88',master_user='repl',master_password='XXXXXXXXXXXXXXXX', master_log_file='mysql-bin.000006',master_log_pos=2256; START SLAVE

(3) execute the SQL command on the host xy-brmms-db1b: SHOW SLAVE STATUS; determines whether the replication in the direction of xy-brmms-db1a- > xy-brmms-db1b is successful according to the output information

(4) complete the replication of xy-brmms-db1a- > xy-brmms-db1b through the above three steps, and then repeat the above three steps to replace the relevant information to complete the replication of xy-brmms-db1b- > xy-brmms-db1a.

Remarks:

For scenarios that may be encountered in MySQL production environment, you need to build replication steps and detailed methods. You can refer to the tutorial on Building MySQL replication in production Environment.

The account number that the operating system needs to add

According to the actual access and application needs, the server operating system access account, each host has 3-5 accounts, and each account will have its own corresponding password. For an application cluster, the accounts and passwords of all database servers are the same. In other cases, the account names of different server operating systems are the same, but the passwords must be different.

In order to avoid the confusion of user and group ID, the user name id and the user group id are uniformly defined:

The uid of the admin user must be: 501

The gid of the admin user group must be: 501

The corresponding shell command is:

Groupadd-g 501 admin useradd-c "dragoon and other software owner"-g admin-u 501 admin

The uid of the mysql user must be: 601

The gid of the mysql user group must be: 601

The corresponding shell command is:

Groupadd-g 601 mysql useradd-c "mysql software owner"-g mysql-u 601 mysql

Remarks:

When nagios is used as a monitoring tool, a user and user group named nagios and group name nagios,ID with the same number 401 will be created, as well as other accounts, all in a similar way.

The above is all the contents of the article "how to initialize the MySQL database server". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.