Mysql installation and master-slave configuration _ MySQL

For mysql installation and master-slave configuration, bitsCN.com is configured remotely by using ssh. 1. install MySQLsudo yum install mysql-server
Sudo chgrp-R mysql/var/lib/mysql
Sudo chmod-R 770/var/lib/mysqlsudo service mysqld start the first installation and setup password: mysql_secure_installation
Press enter and enter Y as prompted.
Enter the password twice and press enter.
Enter Y as prompted
Thanks for using MySQL!

2. create a new user test on MySQL mysql> grant all privileges on mysql. * to test@192.168.100.206 identified by 'mysql '; here, by the way, the command for canceling authorization is revoke, and then the "to" command in the statement is changed to "from" to revoke the granted permission. the command for deleting a user is drop user username @ '%'. the following content is in 3. mySQL master-slave configuration grant all on *. * to user1@192.168.100.205 identified by "mysql"; 192.168.100.205 slave (slave server) IP MySQL login from the MySQL master server sudo mysql-uuser1-pmysql-h192.168.100.206
4. configure the master server my. cnf

[Mysqld]
Server-id = 1
Log-slow-queries = mysql-slow.log
Log-error = mysql. err
Log = mysql. log
Log-bin = mysql-bin
Datadir =/var/lib/mysql
Socket =/var/lib/mysql. sock
User = mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
Symbolic-links = 0

[Mysqld_safe]
Log-error =/var/log/mysqld. log
Pid-file =/var/run/mysqld. pid

Slave server my. cnf configuration
[Mysqld]
Port = 3306
Server-id = 2
Expire_logs_day = 7
Log-slow-queries = mysql-slow.log
Log-error = mysql. err
Log = mysql. log
Datadir =/var/lib/mysql
Socket =/var/lib/mysql. sock
User = mysql

# Character-set-server = utf8

# Collation-server = utf8_general_ci this line and the previous behavior change encoding to UTF8,

# If you want to change the code, you must note that all codes must be modified.
# Disabling symbolic-links is recommended to prevent assorted security risks
Symbolic-links = 0

[Mysqld_safe]
Log-error =/var/log/mysqld. log
Pid-file =/var/run/mysqld. pid

5. mysql Server Backup (master server) # mysqldump-uroot-pmysql test-l-f'/tmp/test. SQL 'this command does not require additional locks for backup.
-F: flush logs-l indicates that the database can only perform read operations.
Tips: it can be restored through binlog, and flush logs plays an important role. It can also be said that flush is very useful before the operation is completed, it is best to refresh the log. 6. mySQL recovery: mysql-uroot-pmysql test-v-f </tmp/test. SQL-v is used to view detailed information about the import.-f indicates that skip can be used when an error occurs, continue to execute the following statement to restore the binlog-file binary log file mysqlbinlog -- no-defaults binlog-file | mysql-uroot-pmysql, but many times some operations in the binlog log need to be discarded; you need to use different commands to meet your needs: # mysqlbinlog -- no-defaults mysqk-bin.000004 -- start-position = "193" -- stop-position = "280" | more restore data at position. The -- start-position can be removed from the first restoration, or the time can be recovered, for example, -- stop-date = "xxxx-xx: xx' -- start-date = "xxxx-xx: xx' 6. copy the backup files on the master server to scp/tmp/test under the tmp server. SQL 192.168.100.205:/tmp/is then restored from the server. before restoration, it is best to Reset master; mysql-uroot-pmysql test </tmp/test. SQL

Mysql> show slave status/G;
* *************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.100.206
Master_User: user1
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 262
Relay_Log_File: mysqld-relay-bin.000006
Relay_Log_Pos: 407
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: Yes
Slave_ SQL _Running: Yes # YES indicates that the two rows are successful.
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 262
Relay_Log_Space: 708
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_ SQL _Errno: 0
Last_ SQL _Error:
1 row in set (0.00 sec)

The following are some commands and notes used:

Mysql> insert into t1 values (4), (5), (6), (7), (8); insert

Create index in_id on t1 (id );
View the index show index from t1;
Desc t2; View table structure
A data entry/exit of 10 million rows
Insert into t2 values (1), (2), (3), (4 );
Insert into t2 select * from t2;

How many rows can be viewed?
Select count (*) from t2;

Real-time monitoring command: watch-nl ls-lh 1 s refresh once

Alt + F2 enable the second terminal
Change the delimiters:/d // This is not the end, and the execution will not start.
Change back/d;

Free-m memory usage

Create index idx_of_c3 on part_tab (c3)

Default Index File name: ibdatal is in the data directory

Mysql table replication
Mysql> create table t2 like t1;
Copy table data (when the table structure is the same );
Mysql> insert into t2 select * from t1;

I saw this video http://pan.baidu.com/s/1lCASk.

BitsCN.com