Ubuntu installation configuration MySQL detailed

Ubuntu installation configuration MySQL

Three Ways to install:

1. Install sudo apt-get install Mysql-server from the Web. Once the environment variables have been configured automatically, you can use the MySQL command directly.

Note: It is recommended to change the CN in/etc/apt/source.list to us, the U.S. server is much faster than China.

2. Install the offline package, taking mysql-5.0.45-linux-i686-icc-glibc23.tar.gz as an example.

3. Binary Package Installation: The environment variables are configured automatically when the installation is complete, and you can use the MySQL command directly

Online installation and binary package installation is relatively simple, focusing on the installation of offline packages.

1. Groupadd MySQL

2. Mkdir/home/mysql

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

4. 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.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.

scripts/mysql_install_db--user=mysql (Be sure to execute in the MySQL directory, note the output of the text, there are changes to the root password and the command to start MySQL)

11. Set the password for root:./bin/mysqladmin-u root password ' passw0rd '

Configure and manage MSYQL:

1. Modify MySQL Maximum connection number: CP support-files/my-medium.cnf./my.cnf,vim MY.CNF, add or modify max_connections=1024

About My.cnf:mysql Search the My.cnf:/etc,mysql installation directory in the following order, data in the installation directory. /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 boot command to the/etc/rc.local file

5. Allow root Remote login:

1) Native Login Mysql:mysql-u root-p (-P must have); Change 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 [e-mail protected] "192.168.11.205" identified by "Passw0rd" with GRANT option; Flush privileges;

4) Enter MySQL library to see if the data for% of host is added: use MySQL; select * from user;

6. Create a database and create a user:

1) Build database: Create DATABASE test1;

2) Build user, Empower: Grant all privileges on test1.* to [email protected] "%" identified by "Passw0rd" with GRANT option;

3) Delete databases: Drop database test1;

7. Delete permissions:

1) Revoke all privileges in test1.* from [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 mysql:mysql-h ip-u user-p

10. Set character sets (take UTF8 as an example):

1) View current encoding: Show variables like ' character% ';

2) Modify MY.CNF, add Default-character-set=utf8 under [client]

3) under [Server] Add default-character-set=utf8,init_connect= ' Set NAMES utf8; '

4) Restart MySQL.

Note: only modify/etc My.cnf to make the client's settings work, the settings under the installation directory can only make the settings of the server valid.

Binary installation of the modified/ETC/MYSQL/MY.CNF can be

11. Upgrade old data to UTF8 (old data takes latin1 as an example):

1) Export old data: mysqldump--default-character-set=latin1-hlocalhost-uroot-b dbname--tables old_table >old.sql

2) conversion Coding (Linux and Unix): Iconv-t utf-8-F gb2312-c old.sql > New.sql

This assumes that the original table data is gb2312, you can also remove-F, let iconv automatically determine the original character set.

3) Import: Modify New.sql, add a word before inserting or modifying the statement: "SET NAMES UTF8;", and modify all gb2312 for UTF8, save.

Mysql-hlocalhost-uroot-p dbname < New.sql

If the Max_allowed_packet error is reported because the file is too large, the default MySQL parameter is 1M, modify the value in the MY.CNF (need to restart MySQL).

12. Support UTF8 client: Mysql-front,navicat,phpmyadmin,linux Shell (execute set NAMES UTF8 after connection, then can read and write UTF8 data. 10.4 After setting up, you don't have to execute this sentence again.)

13. Backup and Recovery

Backing 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

Recovery database: Mysql-uroot-p < Name.sql

Recovery table: Mysql-uroot-p dbname < Name.sql (database must be specified)

14. Copying

MySQL supports 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 master server dedicated to replication: Grant replication Slave on * * to ' replicationuser ' @ ' 192.168.0.87 ' identified by ' Iverson ';

2). Refreshes 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; Record the values of file and position. Shut down the primary server after logging: mysqladmin-uroot-ppassw0rd shutdown

If the output is empty, the server does not have a binary log enabled, add Log-bin=mysql-bin under [Mysqld] in the my.cnf file, and then restart it.

3). Create a snapshot of the primary server (snapshot)

You need to take a snapshot of the databases that need to be replicated on the primary server, Windows can use the ZIP format, and Linux and UNIX best use the tar command. Then upload the data directory from the server MySQL and unzip it.

CD Mysql-data-dir

Tar cvzf Mysql-snapshot.tar./mydb

Note: The snapshot should not contain any log files or *.info files, but should contain only the data files (*.frm and *.opt) files of the database to be copied.

You can use database backup (mysqldump) to do a data recovery from the server to ensure data consistency.

4). Confirm that the [mysqld]section. my.cnf file on the primary server contains Log-bin options and Server-id, and starts the primary server:

[Mysqld]

Log-bin=mysql-bin

Server-id=1

5). Stop from the server, join Server-id, and then start from the server:

[Mysqld]

server-id=2

Note: Here Server-id is the ID from the server and must not be the same as the primary server and other slave servers.

The read-only option can be added to the configuration file from the server so that only SQL from the primary server is accepted from the server, ensuring that the data is not modified by other means.

6). Use the system real value instead of the option when executing the following statement from the server:

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; Stop from thread: Stop slave; (note: The primary server's firewall should allow 3306 port connections)

Verification: The primary server and the data from the server should be consistent, the primary server inserts the modified delete data will be updated to the server, the table, delete the table, etc. is the same.

Ubuntu installation configuration MySQL detailed