Configure and manage MYSQL in ubuntu

Configuration and management of MYSQL in ubuntu I. Installation of MySQL 1. online installation: Command: sudo apt-get install mysql-server during installation, the system will prompt you to set a password for the "root" user and enter your own password, environment variables are automatically configured after the installation and press on. You can directly use the mysql command. 2. download MySQL installation package offline installation (my downloaded 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 4. copy the mysql-5.5.25-linux2.6-x86_64.tar.gz to the/usr/local directory 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. 10. scripts/mysql_install_db -- user = mysql (1 Run the command in the mysql directory. Note that the output text contains the command to change the root password and start mysql.) 11. set password for root :. /bin/mysqladmin-u root password 'passw0rd '2. MySQL configuration and management 1. modify mysql Max connections: cp support-files/my-medium.cnf. /my. cnf, vim my. cnf, add or modify max_connections = 1024 about my. cnf: mysql searches my in the following order. cnf:/etc, mysql installation directory, and data under the installation directory. /Etc is set globally. 2. start mysql:/usr/local/mysql/bin/mysqld_safe -- user = mysql & view mysql version: mysqladmin-u root-p version note: for online installation or binary installation, run the following command to start and stop mysql:/etc/init. d/mysql start | stop | restart3. stop mysql: mysqladmin-uroot-ppassw0rd shutdown. Note that there is no space after u and p. 4. set mysql auto-start: add the startup command to/etc/rc. 5. allow remote root login: 1) Local mysql login: mysql-u root-p (-p must be available); Change Database: use mysql; 2) from all hosts: grant all privileges on *. * to root @ "%" identified" Passw0rd "with grant option; 3) from the specified host: grant all privileges on *. * to root @ "192.168.16.105" identified by "passw0rd" with grant option; flush privileges; 4) check whether the data with host % is added to the mysql database: use mysql; select * from user; 6. create a database, create a user: 1) create a database: create database test1; 2) create a user and grant permissions: grant all privileges on test1. * to user_test @ "%" identified by "passw0rd" with grant option; 3) delete a database: drop database test1; 7. delete permission: 1) revo Ke all privileges on test1. * fromtest1 @ "%"; 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 database: show tables; 9. remotely log on to mysql: mysql-h ip-u user-p10. set the character set (take utf8 as an example): 1) view the current encoding: show variables like 'character % '; 2) modify my. cnf: Add default-character-set = utf8 3 under [client]) add default-character-set = utf8, init_connect = 'set NAMES utf8 under [server]; '4) Restart mysql. Note: Only my. cnf under/etc can make the client settings take effect. The settings under the installation directory can only make the server settings valid. Modify/etc/mysql/my. cnf: 11. upgrade old data to utf8 (latin1 is used as an example): 1) Export old data: mysqldump -- default-character-set = latin1-hlocalhost-uroot-B dbname -- tables old_table> old. SQL 2) Conversion encoding (Linux and UNIX): iconv-t UTF-8-f gb2312-c old. SQL> new. SQL assumes that the data in the original table is gb2312. You can also remove-f to enable iconv to automatically determine the original character set. 3) Import: Modify new. SQL, add a sentence "SET NAMES utf8;" before the insert or modify statement, modify all gb2312 to utf8, and save the statement. Mysql-hlocalhost-uroot-p dbname <new. if SQL reports max_allowed_packet error, it is because the file is too large. The default mysql parameter is 1 MB. modify my. the value in cnf (mysql needs to be restarted ). 12. Clients supporting utf8: Mysql-Front, Navicat, PhpMyAdmin, and Linux Shell (run set names utf8 after connection; then you can read and write utf8 data. 10.4 you do not need to execute this statement after setting.) 13. back up and recover a single database: mysqldump-u root-p-B dbname> dbname. SQL backup all databases: mysqldump-u root-p -- all-databases> all. SQL backup table: mysqldump-u root-p-B dbname -- table tablename> tablename. SQL restoration Database: mysql-u root-p <name. SQL recovery table: mysql-u root-p dbname <name. SQL (database must be specified) 14. mysql replication supports one-way asynchronous replication, that is, one server is the master server, and one or more other servers are the slave server. Replication is implemented through binary logs. The master server writes data and reads data from the server. Multiple master servers can be implemented, but problems that are not encountered by a single server are not recommended ). 1 ). create a User Dedicated to replication on the master server: grant replication slave on *. * to 'replicationuser' @ '192. 168.0.87 'identified by 'iverson'; 2 ). refresh all the tables and block write statements on the MASTER server: flush tables with read lock; then read the binary file name and branch on the MASTER server: show master status; record the values of File and Position. Disable the master server after logging: mysqladmin-uroot-ppassw0rd shutdown. If the output is empty, binary logs are not enabled on the server. add log-bin = mysql-bin under [mysqld] In the cnf file, which will be available after restart. 3). To create a snapshot for the master server (snapshot), you need to create a snapshot for the database to be copied on the master server. For Windows, you can use the zip format. For Linux and Unix, it is best to use the tar command. Then upload the data to the mysql data directory on the slave server and decompress the package. Cd mysql-data-dir tar cvzf mysql-snapshot.tar. /mydb note: the snapshot should not contain any log files or *. info file, only the data file of the database to be copied (*. frm and *. opt) file. You can use mysqldump to recover data from the slave server to ensure data consistency. 4 ). confirm my. the [mysqld] section of the cnf file contains the log-bin option and server-id, and starts the master server: [mysqld] log-bin = mysql-bin server-id = 1 5 ). stop the slave server, add the server-id, and then start the slave server: [mysqld] server-id = 2 Note: the server-id here is the id of the slave server, it must be different from the master server and other slave servers. You can add the read-only option to the configuration file of the server. In this way, the slave server only accepts the SQL statements of the self-owned server to ensure that the data is not modified by other methods. 6 ). run the following statement on the slave server and replace the option with the actual system 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 slave thread: mysql> START slave; stop SLAVE thread: stop slave; (Note: the firewall of the master server should allow port 3306 to connect to the following articles mainly introduce three useful solutions for installing and configuring the MySQL database in Ubuntu, the following section describes how to correctly configure the MySQL database and how to correctly manage the MySQL database. Three installation methods: 1. install sudo apt-get install mysql-server from the Internet. After installing the environment variables that have been automatically configured for Mysql, you can directly use the mysql command. Note: We recommend that you change cn in/etc/apt/source. list to us, and the servers in the United States are much faster than those in China. 2. Install the release package. Take mysql-5.0.45-linux-i686-icc-glibc23.tar.gz as an example. 3. Install the Binary Package: After installation is complete, Mysql environment variables have been automatically configured. You can directly use the mysql command to install the package online and Binary Package. The installation is relatively simple, with emphasis on installing the offline package. 1. groupadd mysql2. mkdir/home/mysql3. useradd-g MySQL-d/home/MySQLmysql4. copy mysql-5.0.45-linux-i686-icc-glibc23.tar.gz to/usr/local directory 5. unzip: tar zxvf mysql-5.0.45-linux-i686-icc-glibc23.tar.gz6. ln-s mysql-5.0.45-linux-i686-icc-glibc23 mysql7. cd/usr/local/mysql8. chown-R MySQL.9. chgrp-R MySQL.10. scripts/mysql_install_db -- user = MySQL (must be executed in the mysql directory, note that the output text contains the command to change the root password and start mysql.) 11. set as root Set Password :. /bin/mysqladmin-u root password 'passw0rd 'configure MySQL and manage MySQL: 1. modify mysql Max connections: cp support-files/my-medium.cnf. /my. cnf, vim my. cnf, add or modify max_connections = 1024 about my. cnf: mysql searches my in the following order. cnf:/etc, mysql installation directory, and data under the installation directory. /Etc is set globally. 2. start mysql:/usr/local/mysql/bin/mysqld_safe -- user = MySQL & view mysql version: mysqladmin-u root-p version note: for online installation or binary installation, run the following command to start and stop mysql:/etc/init. d/MySQLstart | stop | restart3. stop mysql: mysqladmin-uroot-ppassw0rd shutdown. Note that there is no space after u or p. 4. set mysql auto-start: add the startup command to/etc/rc. 5. allow remote root login: 1) Local mysql login: MySQL-u root-p (-p must be available); Change Database: use mysql; 2) from all hosts: grant all privileges on *. * to root @ "%" identified by "passw0r D "with grant option; 3) from the specified host: grant all privileges on *. * to root @ "192.168.11.205" identified by "passw0rd" with grant option; flush privileges; 4) check whether the data with host % is added to the mysql database: use mysql; select * from user; 6. create a database, create a user: 1) create a database: create database test1; 2) create a user and grant permissions: grant all privileges on test1. * to user_test @ "%" identified by "passw0rd" with grant option; 3) delete a database: drop database test1; 7. delete permission: 1) revoke all privi Leges on test1. * from test1 @ "%"; 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 database: show tables; 9. remotely log on to mysql: MySQL-h ip-u user-p10. set the character set (take utf8 as an example): 1) view the current encoding: show variables like 'character % '; 2) modify my. cnf, add default-character-set = utf83 under [client]) add default-character-set = utf8, init_connect = 'set NAMES utf8; '4) under [server) restart mysql. Note: Only my. cnf under/etc can make the client settings take effect. The settings under the installation directory can only make the server settings valid. Binary installation modification/etc/mysql/my. cnf: 11. upgrade old data to utf8 (latin1 is used as an example): 1) Export old data: mysqldump -- default-character-set = latin1-hlocalhost-uroot-B dbname -- tables old_table> old. sql2) Conversion encoding (Linux and UNIX): iconv-t UTF-8-f gb2312-c old. SQL> new. SQL assumes that the data in the original table is gb2312. You can also remove-f to enable iconv to automatically determine the original character set. 3) Import: Modify new. SQL, add a sentence "SET NAMES utf8;" before the insert or modify statement, modify all gb2312 to utf8, and save the statement. MySQL-hlocalhost-uroot-p dbname <new. if SQL reports max_allowed_packet error, it is because the file is too large. The default mysql parameter is 1 MB. modify my. the value in cnf (mysql needs to be restarted ). 12. Clients supporting utf8: Mysql-Front, Navicat, PhpMyAdmin, and Linux Shell (run set names utf8 after connection; then you can read and write utf8 data. 10.4 you do not need to execute this statement after setting.) 13. back up and recover a single database: mysqldump-uroot-p-B dbname> dbname. SQL backup all databases: mysqldump-uroot-p -- all-databases> all. SQL backup table: mysqldump-uroot-p-B dbname -- table tablename> tablename. SQL restoration Database: MySQL-uroot-p <name. SQL recovery table: MySQL-uroot-p dbname <name. SQL (database must be specified) 14. mysql replication supports one-way asynchronous replication, that is, one server is the master server, and one or more other servers are the slave server. Replication is implemented through binary logs. The master server writes data and reads data from the server. Multiple master servers can be implemented, but problems that are not encountered by a single server are not recommended ). 1 ). create a User Dedicated to replication on the master server: grant replication slave on *. * to 'replicationuser' @ '192. 168.0.87 'identified by 'iverson'; 2 ). refresh all the tables and block write statements on the MASTER server: flush tables with read lock; then read the binary file name and branch on the MASTER server: show master status; record the values of File and Position. Disable the master server after logging: mysqladmin-uroot-ppassw0rd shutdown. If the output is empty, binary logs are not enabled on the server. add log-bin = mysql-bin under [mysqld] In the cnf file, which will be available after restart. 3). To create a snapshot for the master server (snapshot), you need to create a snapshot for the database to be copied on the master server. For Windows, you can use the zip format. For Linux and Unix, it is best to use the tar command. Then upload the data to the mysql data directory on the slave server and decompress the package. Cd mysql-data-dir tar cvzf mysql-snapshot.tar. /mydb note: the snapshot should not contain any log files or *. info file, only the data file of the database to be copied (*. frm and *. opt) file. You can use mysqldump to recover data from the slave server to ensure data consistency. 4 ). confirm my. the [mysqld] section of the cnf file contains the log-bin option and server-id, and starts the master server: [mysqld] log-bin = mysql-bin server-id = 1 5 ). stop the slave server, add the server-id, and then start the slave server: [mysqld] server-id = 2 Note: the server-id here is the id of the slave server, it must be different from the master server and other slave servers. You can add the read-only option to the Mysql configuration file on the server. In this way, the slave server only accepts the SQL statements on its own server to ensure that the data is not modified by other methods. 6 ). run the following statement on the slave server and replace the option with the actual system 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 slave thread: mysql> START slave; stop SLAVE thread: stop slave; (Note: The firewall of the master server should allow port 3306 connections) Verification: at this time, the data on the master server and the slave server should be consistent. The data inserted, modified, and deleted on the master server will be updated to the slave server, and the table creation and deletion will be the same.