"Go" MySQL Installation and configuration

First, the installation of MySQL

1. Online Installation:

Command:sudo apt-get install mysql-server

During installation, you will be prompted to set a password for the "root" user, enter your own password, and the installation will automatically configure the environment variables to use the mysql command directly.

2. Download the MySQL installation package offline installation (i downloaded the version is:mysql-5.5.25-linux2.6-x86_64.tar.gz):

1. Groupadd MySQL
　　 2. Mkdir/home/mysql
　　 3. Useradd-g mysql-d/home/mysql MySQL
　　 4. Copy mysql-5.5.25-linux2.6-x86_64.tar.gzto the/usr/localCatalogue
　　 5.Unzip:Tar zxvf mysql-5.5.25-linux2.6-x86_64.tar.gz
　　 6. Ln-s mysql-5.5.25-linux2.6-x86_64 MySQL
　　 7. Cd/usr/local/mysql
　　 8. chown-r MySQL.
　　 9. chgrp-r MySQL.
　　 Ten. scripts/mysql_install_db--user=mysql (must be inMySQLdirectory execution, note the output of the text, there are changes insideRootPassword and startupMySQL's command)
　　 One by one .to beRootSet Password:./bin/mysqladmin-u root password ' passw0rd '

II, mysql configuration and management
1.  mysql

CP support-files/my-medium.cnf./my.cnf,vim my.cnf, add or modify max_connections=1024
About my.cnf:mysql searches the my.cnf:/etc,mysql installation directory in the following order, in the installation directory Data. / etc is the global setting.

2. start mysql:

/usr/local/mysql/bin/mysqld_safe--user=mysql &
View mysql versions:mysqladmin-u root-p version
Note: Online installation or binary installation can start and stop MySQL directly using the following command :/etc/init.d/mysql start|stop|restart

3. stop mysql:

Mysqladmin-uroot-ppassw0rd shutdown Note that There are no spaces after u,p

4. set mysql self-boot:

Add the Start command to the /etc/rc.local file

5.AllowRootRemote Login:
　 1) Native LoginMySQL:mysql-u root-p(- PBe sure to have one); Change the database:Use MySQL;
　 2) from all hosts:Grant all privileges on * * to[email protected]"%" identified by "Passw0rd" with GRANT option;
　 3) from the specified host:Grant all privileges on * * to [email protected] ' 192.168.16.105 ' identified by ' passw0rd ' with GRANT option; Flush PRI Vileges;
　 4)intoMySQLLibrary ViewHostto be%whether the data is added:Use MySQL, select* from user;

6.  Create a database, create a user
Build library: CREATE DATABASE TEST1;
2)   build user, Empower: GRANT ALL PRIVILEGES ON TEST1.* TO  Span class= "__cf_email__" data-cfemail= "FF8A8C9A8DA08B9A8C8BBF" >[email protected] "%"  identified  by  "Passw0rd"  with grant option;
3 ) Delete database: drop database test1;

7. Delete permissions:
　1) Revoke all privileges on test1.* [email protected]"%";
　2) use MySQL;
　3) Delete from user where user= "root" and host= "%";
　4) flush privileges;
8. Show all databases:

show databases; Show all tables in the library: show tables;

9. telnet to mysql:

Mysql-h Ip-u User-p

10.sets the character set (inUTF8as an example):
1) to view the current encoding:Show variables like ' character% ';
2)Modifymy.cnf, in[Client]under AddDefault-character-set=utf8
3) in[Server]under AddDefault-character-set=utf8,init_connect= ' SET NAMES utf8; '
4) RestartMySQL.
Note: only modify/ etcunder themy.cnfto makeClientsettings, the settings in the installation directory only enableServersettings are valid.
Modifications to the binary installation/etc/mysql/my.cnfcan be

11.old data upgrade toUTF8(old data tolatin1as an example):
1) to export old data:mysqldump--default-character-set=latin1-hlocalhost-uroot-b dbname--tables old_table >old.sql
2) Conversion Encoding(Linuxand theUNIX):iconv-t utf-8-F gb2312-c old.sql > New.sql
This assumes that the data for the original table isgb2312, you can also remove- F, letIconvautomatically determine the original character set.
　 3) Import: ModifyNew.sql, add a sentence before inserting or modifying the statement:"SET NAMES UTF8;", and modify all thegb2312to beUTF8, save.
mysql-hlocalhost-uroot-p dbname < New.sql
If the reportMax_allowed_packetthe error is because the file is too large,MySQLThe default parameter is1M, modifymy.cnfthe value in the (need to restartMySQL).

Support for UTF8 clients:

       mysql-front,navicat,phpmyadmin linux shell SET&NBSP;NAMES&NBSP;UTF8; utf8 the data. 10.4 When you're done, you don't have to do this again.)

Backup and Recovery
Backing up a single database:mysqldump-u root-p-B dbname > Dbname.sql
Back up all databases:mysqldump-u root-p--all-databases > All.sql
Backup table: mysqldump-u root-p-B dbname--table tablename > Tablename.sql
Recovery database:mysql-u root-p < Name.sql
Recovery table:mysql-u root-p dbname < Name.sql ( database must be specified )

14.Copy
　　 MysqlSupport One-way asynchronous replication, that is, one server master server, the other one or more servers do from the server. Replication is implemented through a binary log, which is written by the primary server and read from the server. You can implement multiple primary servers, but you will encounter problems that a single server has not encountered (not recommended).
　　 1).Create a user on the primary server dedicated to replication:grant replication Slave on * *To ' replicationuser ' @ ' 192.168.0.87 ' identified by ' Iverson ';
　　 2).Flush all table and block write statements on the primary server:flush tables with read lock;then read the binary binary filenames and branches on the primary server:SHOW MASTER STATUS;will beFileand thePositionvalues are recorded. Shut down the primary server after logging:mysqladmin-uroot-ppassw0rd shutdown
If the output is empty, the server does not have binary logs enabled,my.cnfin the file[Mysqld]under AddLog-bin=mysql-bin, which is available after a reboot.
　　 3).establish a snapshot of the primary server (Snapshot)
You need to take a snapshot of the database that needs to be replicated on the primary server,Windowscan useZipformat,Linuxand theUnixBest to useTarcommand. Then upload it to the slave server.MySQLThe data directory and unzip it.
　　　 CD Mysql-data-dir
　　　 tar cvzf mysql-snapshot.tar./mydb
Note: The snapshot should not contain any log files or*.infofile, which should contain only the data files of the database to be copied (*.frmand the*.opt) file.
Can be backed up with a database(mysqldump)to make data recovery from the server, ensure the consistency of data.
4).Verify that the primary servermy.cnfof the file[Mysqld]sectionincludeLog-binoptions, andServer-id, and start the primary server:
　　　 [Mysqld]
　　　 Log-bin=mysql-bin
　　　 server-id=1
　　 5).stop from the server, joinServer-id, and then start from the server:
　　　 [Mysqld]
　　　 server-id=2
Note: Here'sServer-idis from the server'sID, must not be the same as the primary server and other slave servers.
Can be added in the configuration file from the serverread-onlyoption, so that only the servers from the primary server accept theSQLto ensure that the data is not modified by other passes.
　　 6).execute the following statement from the server, replacing the option with the system's True value:
　　　 Change master to master_host= ' Master_host ', master_user= ' replication_user ', master_password= ' replication_pwd ',
　　　 master_log_file= ' Recorded_log_file_name ', master_log_pos=log_position;
　　 7).start from thread:mysql> START SLAVE;To stop from a thread:stop slave;(Note: The firewall of the primary server should allow3306Port Connection