Introduction to installation and configuration of MySQL under Centos 6.4

Introduction to installation and configuration of MySQL under Centos 6.4

Our previous article has introduced the function is mainly used in the recent company's environmental structure construction, so today's article is mainly for the company's environment to build the test; continue to crap some, because the company's site servers are deployed in China and the United States (two units), China and the United States related users will update the site data, So in order to unify the data on both sides, we need to configure the data synchronization operation, before we introduced to the use of shell synchronization corresponding data directory, and we need to do after the MySQL server data synchronization (master-slave), today we first for the later MySQL configuration master-slave installation and configuration services, For the configuration MySQL master-slave operation the next article will be introduced;

Environment Introduction: 1:os:centos 6.4;ip:192.168.6.28;hostname:db1 2:os:centos 6.4;IP:192.168.6.38;HOSTNAME:DB2

For the installation of the system is not introduced, directly began to install the database

Yum install mysql-server MySQL mysql-devel

650) this.width=650; "title=" clip_image001 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "clip_image001" src= "Http://s3.51cto.com/wyfs02/M01/86/54/wKiom1e79QiyItMFAAFd3eIXiT4782.png" height= "336"/>

650) this.width=650; "title=" clip_image002 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "clip_image002" src= "Http://s3.51cto.com/wyfs02/M00/86/54/wKiom1e79QqT_-2AAAHDFxufyZU575.png" height= "309"/>

After the installation is complete we start the database and start initializing the database

/etc/init.d/mysqld Start or service mysqld start

650) this.width=650; "title=" clip_image003 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "clip_image003" src= "Http://s3.51cto.com/wyfs02/M02/86/53/wKioL1e79QvxWJheAAGrDLnty4Q390.png" height= "347"/>

Database Startup complete

650) this.width=650; "title=" clip_image004 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "clip_image004" src= "http://s3.51cto.com/wyfs02/M02/86/53/wKioL1e79QvDcuoNAAHt4zxB-UY112.png" height= "444"/>

Set up automatic startup for MySQL

Chkconfig mysqld on

650) this.width=650; "title=" clip_image005 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "clip_image005" src= "http://s3.51cto.com/wyfs02/M00/86/54/wKiom1e79QzjI_UdAAA1q5FBwT0867.png" height= "/>"

Restart MySQL Service

Service mysqld Restart

650) this.width=650; "title=" clip_image006 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "clip_image006" src= "http://s3.51cto.com/wyfs02/M01/86/54/wKiom1e79QyDorhIAABaZe9TfIY357.png" height= "/>"

Netstat-anlpt | grep MySQL View port information

650) this.width=650; "title=" clip_image007 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "clip_image007" src= "http://s3.51cto.com/wyfs02/M02/86/54/wKiom1e79Q3yeedzAACoPHnydZY057.png" height= "/>"

To see if the service is in auto-start state

Chkconfig--list | grep MySQL

650) this.width=650; "title=" clip_image008 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "clip_image008" src= "http://s3.51cto.com/wyfs02/M01/86/53/wKioL1e79Q3ygwhJAABGtgVtumg440.png" height= "/>"

MySQL database after installation will only have a root administrator account, but at this time the root account has not set a password for it, the first time the MySQL service started, the database will be some initialization work, in the output of a large string of information, we see a line of information:

/usr/bin/mysqladmin-u root password ' new-password '//Set password for root account

650) this.width=650; "title=" clip_image009 "style=" border:0px;padding-top:0px;padding-left:0px;margin:0px; Padding-right:0px;width:500px;height:44px;float:left;background-image:none; "border=" 0 "alt=" clip_image009 "src=" Http://s3.51cto.com/wyfs02/M00/86/54/wKiom1e79Q6w6IijAAA8Hf7BgTw644.png "height=" "width=" "hspace=" 0 "vspace = "0"/>

So we can use this command to set the password for our root account (Note: This root account is the root account of MySQL, not the root account of Linux)

Mysqladmin-u root password ' root '//The root account is set to root by this command password is rooted we can now log in to our MySQL database by mysql-u root-p command.

650) this.width=650; "title=" clip_image010 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "clip_image010" src= "http://s3.51cto.com/wyfs02/M01/86/54/wKiom1e79Q_ja8fgAAD2qKNPalw680.png" height= "/>"

show databases;

650) this.width=650; "title=" clip_image011 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "clip_image011" src= "Http://s3.51cto.com/wyfs02/M00/86/54/wKiom1e79RDBJGYSAABPONgI5KM274.png" height= "109"/>

V. Major configuration files for MySQL database

1./ETC/MY.CNF This is the main configuration file for MySQL

We can take a look at some information about this file

650) this.width=650; "title=" image "style=" border-top:0px;border-right:0px;background-image:none;border-bottom:0 px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt=" image "src=" Http://s3.51cto.com/wyfs02/M02/86/53/wKioL1e79RCTZBwMAADJG_jXTtQ230.png "height=" 271 "/>

2./var/lib/mysql Database file storage location for MySQL database

The database files for our MySQL database are usually stored in the/ver/lib/mysql directory.

cd/var/lib/mysql/

-RW-RW----. 1 MySQL mysql 10485760 April 6 22:01 ibdata1
-RW-RW----. 1 MySQL mysql 5242880 April 6 22:01 Ib_logfile0
-RW-RW----. 1 MySQL mysql 5242880 April 6 21:59 ib_logfile1
DRWX------. 2 MySQL mysql 4096 April 6 21:59 MySQL//These two are the default two database files when the MySQL database is installed
Srwxrwxrwx. 1 mysql MySQL 0 April 6 22:01 mysql.sock
DRWX------. 2 MySQL mysql 4096 April 6 21:59 test//These two are the default two database files when the MySQL database is installed

We can create a database ourselves to verify where the database files are stored

Create a database of our own:

mysql> CREATE DATABASE ixmsoft2016; Query OK, 1 row Affected (0.00 sec)

650) this.width=650; "title=" clip_image013 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "clip_image013" src= "Http://s3.51cto.com/wyfs02/M02/86/53/wKioL1e79RGx_3c0AAA7WskJgbY026.png" height= "Bayi"/>

If you want to delete the database,

drop database ixmsoft2016;show databases;

650) this.width=650; "title=" clip_image014 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "clip_image014" src= "Http://s3.51cto.com/wyfs02/M00/86/53/wKioL1e79RGSxXIyAABWVuhciHY934.png" height= "162"/>

Enter MySQL's Data directory

Cd/var/lib/mysql

Ls-l
-RW-RW----. 1 MySQL mysql 10485760 April 6 22:01 ibdata1
-RW-RW----. 1 MySQL mysql 5242880 April 6 22:01 Ib_logfile0
-RW-RW----. 1 MySQL mysql 5242880 April 6 21:59 ib_logfile1
DRWX------. 2 MySQL mysql 4096 April 6 21:59 MySQL
Srwxrwxrwx. 1 mysql MySQL 0 April 6 22:01 mysql.sock
DRWX------. 2 MySQL mysql 4096 April 6 21:59 test
DRWX------. 2 MySQL mysql 4096 April 6 22:15 Xiaoluo//This is the Xiaoluo database we just created.

650) this.width=650; "title=" clip_image015 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "clip_image015" src= "Http://s3.51cto.com/wyfs02/M01/86/54/wKiom1e79RKRFu8pAAD9CIFZzpA228.png" height= "168"/>

3./var/log MySQL database log output storage location

Some of our MySQL database's log output is stored in the/var/log directory

Cd/var/log

Ls
Amanda Cron maillog-20130331 Spice-vdagent.log
Anaconda.ifcfg.log cron-20130331 mcelog Spooler
Anaconda.log cups Messages spooler-20130331
Anaconda.program.log dirsrv messages-20130331 SSSD
Anaconda.storage.log DMESG Mysqld.log Tallylog
Anaconda.syslog Dmesg.old ntpstats Tomcat6
Anaconda.xlog Dracut.log Piranha Wpa_supplicant.log
Anaconda.yum.log GDM Pm-powersave.log wtmp
Audit httpd PPP Xorg.0.log
Boot.log Ibacm.log PreLink Xorg.0.log.old
Btmp Lastlog SA Xorg.1.log
btmp-20130401 Libvirt Samba Xorg.2.log
Cluster Luci Secure Xorg.9.log
Consolekit Maillog secure-20130331 Yum.log

Next we create the table

Mysql-u root-p

We use the tool to connect, prompt to reject the connection, the default is to deny the connection;

650) this.width=650; "title=" clip_image016 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "clip_image016" src= "Http://s3.51cto.com/wyfs02/M02/86/54/wKiom1e79ROTJKn1AAEPMiC8PEg198.png" height= "334"/>

Settings allow any host to connect to the MySQL server as root;

GRANT all privileges on * * to ' root ' @ '% ' identified by ' with GRANT OPTION; GRANT all privileges on * * to ' root ' @ '% ' identified by ' with GRANT Option;creata database DB1; Create a database with the name DB1

650) this.width=650; "title=" clip_image017 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "clip_image017" src= "http://s3.51cto.com/wyfs02/M02/86/53/wKioL1e79RPRmD07AABBbprSRxM399.png" height= "/>"

show databases;

View Database

650) this.width=650; "title=" clip_image018 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "clip_image018" src= "Http://s3.51cto.com/wyfs02/M00/86/54/wKiom1e79RWy3KNcAABskYLOkD8736.png" height= "239"/>

Use DB1;

Select Database DB1;

650) this.width=650; "title=" clip_image019 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "clip_image019" src= "http://s3.51cto.com/wyfs02/M02/86/53/wKioL1e79RWi5RZZAAAiaLXT3P4691.png" height= "/>"

Show tables;

Show Table is empty

650) this.width=650; "title=" clip_image020 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "clip_image020" src= "http://s3.51cto.com/wyfs02/M01/86/54/wKiom1e79RazQ_6CAAAqvBRzIvM547.png" height= "/>"

We create a table whose name is info, in the following format:

CREATE TABLE info (id int () not null,name varchar (in) not null,sex varchar ($) Not null,mail varchar (255) Not null,primary KEY (ID));

650) this.width=650; "title=" clip_image021 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "clip_image021" src= "http://s3.51cto.com/wyfs02/M01/86/53/wKioL1e79RaxrDr9AABD1HT-2_U555.png" height= "/>"

Show tables;

650) this.width=650; "title=" clip_image022 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "clip_image022" src= "Http://s3.51cto.com/wyfs02/M02/86/54/wKiom1e79RfyQsmYAABiQZqJhVw140.png" height= "178"/>

SELECT * from info;

650) this.width=650; "title=" clip_image023 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "clip_image023" src= "http://s3.51cto.com/wyfs02/M00/86/54/wKiom1e79RejJjAeAAAu9Q4fYbw101.png" height= "/>"

We connect through management tools

Settings allow any host to connect to the MySQL server as root, and the default is not to allow connections;

GRANT all privileges on * * to ' root ' @ '% ' identified by ' with GRANT OPTION;

650) this.width=650; "title=" clip_image024 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "clip_image024" src= "Http://s3.51cto.com/wyfs02/M01/86/53/wKioL1e79RiB6RPNAAFuyFBl8-g173.png" height= "407"/>

We insert data;

Insert into info (name,sex,mail) VALUES (' Lisi ', ' Boy ', ' [email protected] ') and insert into info (name,sex,mail) VALUES (' Xiaoli ', ' Girl ', ' [email protected] ';

650) this.width=650; "title=" clip_image025 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "clip_image025" src= "Http://s3.51cto.com/wyfs02/M02/86/53/wKioL1e79Rmh0Ub0AADDhDOOfmA571.png" height= "135"/>

SELECT * from info;

650) this.width=650; "title=" clip_image026 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "clip_image026" src= "Http://s3.51cto.com/wyfs02/M01/86/54/wKiom1e79SrzkYzdAAB8yDOs2lE032.png" height= "172"/>

We're inserting a piece of data again.

Insert into info (name,sex,mail) VALUES (' Zhangsan ', ' Boy ', ' [email protected] ');

650) this.width=650; "title=" clip_image027 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "clip_image027" src= "Http://s3.51cto.com/wyfs02/M01/86/53/wKioL1e79SvzfgDeAACLzcXhXFI073.png" height= "181"/>

We update the content using the update command

Update info Set name = ' ZS ' where id = 6;

650) this.width=650; "title=" clip_image028 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "clip_image028" src= "http://s3.51cto.com/wyfs02/M01/86/53/wKioL1e79SvTlQ1JAABcuextmfU552.png" height= "/>"

650) this.width=650; "title=" clip_image029 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "clip_image029" src= "Http://s3.51cto.com/wyfs02/M02/86/54/wKiom1e79SzD-pQLAACInzPQoEw219.png" height= "176"/>

If you delete all the data in the table,

Drop Fron info;

This clears all the data in the table;

Let's say it again, MySQL database data file location

/var/lib/mysql/

650) this.width=650; "title=" clip_image030 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px, "border=" 0 "alt=" clip_ image030 "src=" Http://s3.51cto.com/wyfs02/M01/86/54/wKiom1e79S2zN6j1AAF_aLxRq5Q468.png "height=" 243 "/>

This article from "Gao Wenrong" blog, declined reprint!

Introduction to MySQL Installation and configuration under Centos 6.4