Create MySQL slave database and MySQL

Create MySQL slave database and MySQL

We know that Oracle has DataGuard to back up data in real time, which can be used for master-slave switchover. MySQL also has its own backup database solution, which is called master-slave replication.

The MySQL slave database is set up to synchronize data in the master database in real time. It can also share the read Pressure on the master database and form a read/write splitting structure for the database.

Note:

1. The server-id of the master database and slave database must not be the same.

2. Create a replication slave account in the master database.

Grant replication slave on *. * to 'repl' @ '192. 168.0.20.' identified 'oracle ';

3. View master Status of the master database

Mysql> show master status \ G
* *************************** 1. row ***************************
File: mysql-bin.000005
Position: 251651
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)

4. Configure slave Database

Change master
-> Master_host = '1970. 168.0.20 ',
-> Master_user = 'repl ',
-> Master_password = 'oracle ',
-> Master_log_file = 'mysql-bin.000005 ',
-> Master_log_pos = 251651;

5. Start slave Database

Slave start

Show slave status \ G

* *************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host :***********
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 463725968
Relay_Log_File: mysql-relay-bin.000006
Relay_Log_Pos: 463726114
Relay_Master_Log_File: mysql-bin.000005
Slave_IO_Running: Yes
Slave_ SQL _Running: Yes
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: 463725968
Relay_Log_Space: 873569451
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:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 100

Note:

If the slave database Slave_IO_Running: No/Slave_ SQL _Running: No

Disable slave

Set GLOBALSQL_SLAVE_SKIP_COUNTER = 1;
When slave is enabled

Where is the new mysql database saved by default?

If you do not set the installation path, MYSQL is installed in C: \ Program Files \ MySQL Server 5.1 by default, and the new database file is in C: \ Documents and Settings \ All Users \ Application Data \ MySQL Server 5.1 \ data here.

How to Create a mysql database?

1. Use the SHOW statement to find out the current database on the server:
Mysql> show databases;
+ ---------- +
| Database |
+ ---------- +
| Mysql |
| Test |
+ ---------- +
3 rows in set (0.00 sec)

2. Create a database, abccs
Mysql> create database abccs;
Note that different operating systems are case sensitive.
3. Select the database you created
Mysql> USE abccs
Database changed
Now you have entered the database abccs you just created.
4. Create a database table
First, check what tables exist in your database:
Mysql> show tables;
Empty set (0.00 sec)
It indicates that no database table exists in the Database just created. Create a database table mytable: Create a birthday table for your employees. The table contains the employee name, gender, birth date, and city of birth.
Mysql> create table mytable (name VARCHAR (20), sex CHAR (1 ),
-> Birth DATE, birthaddr VARCHAR (20 ));
Query OK, 0 rows affected (0.00 sec)

Because the column values of name and birthadd change, VARCHAR is selected and its length is not necessarily 20. You can choose any length from 1 to 255. If you need to change its font length in the future, you can use the alter table statement .); Gender can be expressed by only one character: "m" or "f". Therefore, CHAR (1) is used, and DATE is used for the birth column.
After creating a table, we can look at the results just now and use show tables to SHOW which TABLES are in the database:
Mysql> show tables;
+ --------------------- +
| Tables in menagerie |
+ --------------------- +
| Mytables |
+ --------------------- +

5. display table structure:
Mysql> DESCRIBE mytable;
+ ------------- + ------ + ----- + --------- + ------- +
| Field | Type | Null | Key | Default | Extra |
+ ------------- + ------ + ----- + --------- + ------- +
| Name | varchar (20) | YES | NULL |
| Sex | char (1) | YES | NULL |
| Birth | date | YES | NULL |
| Deathaddr | varchar (20) | YES | NULL |
+ ------------- + ------ + ----- + --------- + ------- +
4 rows in set (0.00 sec)

6. Add records to the table
We first use the SELECT command to view the data in the table:
Mysql> select * from mytable;
Empt ...... remaining full text>