MYSQL configuration and management in ubuntu _ MySQL

Configure and manage MYSQL in ubuntu

BitsCN.com

Configure and manage MYSQL in ubuntu

1. install MySQL

1. online installation:

Command: sudo apt-get install mysql-server

During the installation process, you will be prompted to set a password for the "root" user. enter your own password. after the installation is completed, environment variables are automatically configured. you can directly use the mysql command.

2. download the 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 the 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 (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'

II. MySQL configuration and management

1. modify the maximum number of mysql 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 and p

4. set mysql to start automatically:

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 (-p must be available); 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.16.105" 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. * fromtest1 @ "% ";

2) use mysql;

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

4) flush privileges;

8. display all databases:

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

9. remotely log on to mysql:

Mysql-h ip-u user-p

10. set the character set (take utf8 as an example ):

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.

Modify the/etc/mysql/my. cnf file in the operating system.

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 (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 don't need to execute this statement after the setting is complete)

13. backup and recovery

Back 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

Recover database: mysql-u root-p <name. SQL

Recovery Table: mysql-u root-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. 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. 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 (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 files, but only the data files (*. frm and *. opt) 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 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). 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 be connected.

The following articles mainly introduce three useful solutions for installing and configuring the MySQL database in Ubuntu, as well as how to correctly configure the MySQL database and how to correctly manage the MySQL database, the following describes the main content of the article. I hope you will have a better understanding of it.

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/MySQLmysql

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 MySQL:

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/MySQLstart | 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 (-p must be available); 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. set the character set (take utf8 as an example ):

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 (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 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. 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. 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 (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 files, but only the data files (*. frm and *. opt) 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 be connected)

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.

BitsCN.com