3 solutions for installing and configuring MySQL database in Ubuntu

The following article describes how to install and configure a MySQL database in Ubuntu and how to configure the actual operation procedures and precautions of the MySQL database and how to correctly manage the msyql database, the following is the main content of the article.

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. Binary Package installation: MySQL environment variables have been automatically configured after installation. You can directly use the MySQL Command

Online installation and Binary Package installation are relatively simple, with emphasis on the installation of offline packages.

1. groupadd MySQL

2. mkdir/home/MySQL

3. useradd-g MySQL-d/home/MySQL

4. copy the MySQL-5.0.45-linux-i686-icc-glibc23.tar.gz to the/usr/local directory

5. Unzip the tar zxvf MySQL-5.0.45-linux-i686-icc-glibc23.tar.gz

6. ln-s MySQL-5.0.45-linux-i686-icc-glibc23 MySQL

7. cd/usr/local/MySQL

8. chown-R MySQL.

9. chgrp-R MySQL.

10. scripts/MySQL_install_db -- user = MySQL (Be sure to 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 the password for root:./bin/MySQLadmin-u root password 'passw0rd'

Configure MySQL and manage msyql:

1. Modify MySQL Max connections: cp support-files/my-medium.cnf./my. cnf, vim my. cnf, add or modify max_connections = 1024

For my. cnf: MySQL, search for my. cnf:/etc, MySQL installation directory, and data under 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 | restart

3. 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 the/etc/rc. local file.

5. Allow remote root login:

1) log on to MySQL on the local machine: MySQL-u root-p must exist); change the database: use MySQL;

2) from all hosts: grant all privileges on *. * to root @ "%" identified by "passw0rd" with grant option;

3) grant all privileges on *. * to root @ "192.168.11.205" identified by "passw0rd" with grant option; flush privileges;

4) Go to the MySQL database to check whether the data with host % is added: use MySQL; select * from user;

6. Create a database and create a user:

1) database creation: 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 privileges on test1. * from test1 @ "% ";

2) use MySQL;

3) delete from user where user = "root" and host = "% ";

4) flush privileges;

8. display all databases: show databases; display all tables in the database: show tables;

9. remotely log on to MySQL: MySQL-h ip-u user-p

10. utf8 is used as an example to set the character set ):

1) view the current encoding: show variables like 'character % ';

2) modify my. cnf and add default-character-set = utf8 under [client ].

3) 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.

Binary installation modification/etc/MySQL/my. cnf

11. upgrade old data to utf8 using latin1 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

Assume 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. SQL

If the error "max_allowed_packet" is reported, it is because the file is too large. The default MySQL parameter is 1 MB. Modify the value in my. cnf to restart MySQL ).

12. Clients supporting utf8: MySQL-Front, Navicat, PhpMyAdmin, and Linux Shell. After the connection, execute set names utf8. Then, you can read and write utf8 data. 10.4 you don't need to execute this statement after the setting is complete)

13. backup and recovery

Back up a single database: MySQLdump-uroot-p-B dbname> dbname. SQL

Back up all databases: MySQLdump-uroot-p -- all-databases> all. SQL

Backup table: MySQLdump-uroot-p-B dbname -- table tablename> tablename. SQL

Recover Database: MySQL-uroot-p <name. SQL

Recovery table: MySQL-uroot-p dbname <name. SQL (the database must be specified)

14. Copy

MySQL 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. It can implement multiple master servers, but it is not recommended to encounter problems that a single server has never encountered ).

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. Close the master server after logging: MySQLadmin-uroot-ppassw0rd shutdown

If the output is empty, binary logs are not enabled on the server. In the my. cnf file, add log-bin = MySQL-bin under [MySQLd], which is displayed after restart.

3). Create a snapshot for the master server)

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 files, but only the data files *. frm and *. opt files of the database to be copied.

You can use MySQLdump to recover data from the slave server to ensure data consistency.

4) Confirm that the [MySQLd] section of the my. cnf file on the master server contains the log-bin option and server-id, and start 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 slave server id, which 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). Execute the following statement on the slave server and replace the option with the system actual 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)

Verification: At this time, the data on the master server and slave server should be consistent. inserting, modifying, and deleting data on the master server will be updated to the slave server to create a table, the same is true for deleting tables.