MySQL installation and master-slave Configuration

I use ssh for remote configuration. The configuration is slightly different.
1. Install MySQL
Sudo yum install mysql-server
Sudo chgrp-R mysql/var/lib/mysql
Sudo chmod-R 770/var/lib/mysql
Sudo service mysqld start
 
Set the password for the first installation: 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 permissions.
 
The command to delete a user is
Drop user username @ '%'
The following content is a test database operation created on MySQL.
3. MySQL Master/Slave Configuration
Grant all on *. * to user1@192.168.100.205 identified by "mysql ";
IP address of 192.168.100.205 slave (slave server)
 
Log on to the MySQL master server from the MySQL 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 read 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 has a great role.
You are advised to refresh the log before completing the operation.
6. MySQL recovery:
Mysql-uroot-pmysql test-v-f </tmp/test. SQL
-V: view the Import Details;
-F: skip can be used to continue executing the following statement when an error occurs.
 
Restore binary log files of binlog-file
Mysqlbinlog -- no-defaults binlog-file | mysql-uroot-pmysql
However, in most cases, some operations in the binlog 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 with the position of 193-280.
-- Start-position can be removed from the first restoration.
 
It can also be restored by time points.
For example: -- stop-date = "xxxx-xx: xx'
-- Start-date = "xxxx-xx: xx'
 
6. copy the files backed up on the master server to the tmp directory of the slave server.
Scp/tmp/test. SQL 192.168.100.205:/tmp/
Then, recover from the server. It is best to Reset the master before restoration;
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;

Recommended reading:

Load Nginx in Ubuntu for high-performance WEB Server 5 --- MySQL master/Master Synchronization

Production Environment MySQL master/Master synchronization primary key conflict handling

MySQL Master/Slave failure error Got fatal error 1236

MySQL master-slave replication, implemented on a single server