CentOS下Mysql简易操作

Mysql

mysql的root密码重置

编辑mysql主配置文件

vim /etc/my.cnf

添加..grant参数

[mysqld]

skip-grant

重启mysql服务

service mysqld restart

进入数据库不用授权

更新root用户密码

/usr/local/mysql/bin/mysql -uroot

use mysql;

update user set password=password('mysql1234') where user='root';

flush privileges;

quit;

注释添加的...grant参数

vim /etc/my.cnf

重启mysql服务

service mysqld restart

重新用新密码登录

/usr/local/mysql/bin/mysql -uroot -pmysql1234

mysql登录

-u用户

-p密码

-h主机

-P端口

-S套接字

默认Mysql的root用户密码为空，登录不用加-p选项

/usr/local/mysql/bin/mysql -uroot -h 127.0.0.1

设置一个密码

/usr/local/mysql/bin/mysqladmin -uroot password 'mysql1234'

再次登录

/usr/local/mysql/bin/mysql -u root -pmysql1234 -h 127.0.0.1

套结字本地登录

/usr/local/mysql/bin/mysql -uroot -S /tmp/mysql.sock -pmysql1234

再设置一个密码就需要输入之前的密码

/usr/local/mysql/bin/mysqladmin -uroot -pmysql1234 password 'mysql12345' -h 127.0.0.1

设置别名

[root@localhost ~]# vim /etc/bashrc

alias mysql=/usr/local/mysql/bin/mysql

alias mysqladmin=/usr/local/mysql/bin/mysqladmin

更改PATH

[root@localhost ~]# vim /etc/profile.d/path.sh

export PATH=$PATH:/usr/local/mysql/bin

[root@localhost ~]# source /etc/profile.d/path.sh

mysql常用操作

查看都有哪些库

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| discuz |

| mysql |

| test |

+--------------------+

4 rows in set (0.00 sec)

查看某个库的表

mysql> use information_schema;show tables;

Database changed

+---------------------------------------+

| Tables_in_information_schema |

+---------------------------------------+

| CHARACTER_SETS |

| COLLATIONS |

| COLLATION_CHARACTER_SET_APPLICABILITY |

| COLUMNS |

| COLUMN_PRIVILEGES |

| ENGINES |

| EVENTS |

| FILES |

| GLOBAL_STATUS |

| GLOBAL_VARIABLES |

| KEY_COLUMN_USAGE |

| PARTITIONS |

| PLUGINS |

| PROCESSLIST |

| PROFILING |

| REFERENTIAL_CONSTRAINTS |

| ROUTINES |

| SCHEMATA |

| SCHEMA_PRIVILEGES |

| SESSION_STATUS |

| SESSION_VARIABLES |

| STATISTICS |

| TABLES |

| TABLE_CONSTRAINTS |

| TABLE_PRIVILEGES |

| TRIGGERS |

| USER_PRIVILEGES |

| VIEWS |

+---------------------------------------+

28 rows in set (0.00 sec)

查看表的字段类型

mysql> desc VIEWS;

+----------------------+--------------+------+-----+---------+-------+

+----------------------+--------------+------+-----+---------+-------+

+----------------------+--------------+------+-----+---------+-------+

10 rows in set (0.00 sec)

查看创建表的语句

mysql> show create table VIEWS;

+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table |

| VIEWS | CREATE TEMPORARY TABLE `VIEWS` (

`TABLE_CATALOG` varchar(512) DEFAULT NULL,

`TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',

`TABLE_NAME` varchar(64) NOT NULL DEFAULT '',

`VIEW_DEFINITION` longtext NOT NULL,

`CHECK_OPTION` varchar(8) NOT NULL DEFAULT '',

`IS_UPDATABLE` varchar(3) NOT NULL DEFAULT '',

`DEFINER` varchar(77) NOT NULL DEFAULT '',

`SECURITY_TYPE` varchar(7) NOT NULL DEFAULT '',

`CHARACTER_SET_CLIENT` varchar(32) NOT NULL DEFAULT '',

`COLLATION_CONNECTION` varchar(32) NOT NULL DEFAULT ''

) ENGINE=MyISAM DEFAULT CHARSET=utf8 |

1 row in set (0.00 sec)

查看创建库的语句

mysql> show create database information_schema;

+--------------------+-----------------------------------------------------------------------------+

| Database | Create Database |

+--------------------+-----------------------------------------------------------------------------+

| information_schema | CREATE DATABASE `information_schema` /*!40100 DEFAULT CHARACTER SET utf8 */ |

+--------------------+-----------------------------------------------------------------------------+

1 row in set (0.00 sec)

查看当前是哪个用户

mysql> select user();

+----------------+

| user() |

+----------------+

| root@localhost |

+----------------+

1 row in set (0.00 sec)

查看当前库

mysql> select database();

+--------------------+

| database() |

+--------------------+

| information_schema |

+--------------------+

1 row in set (0.00 sec)

创建一个库

mysql> create database db1;

Query OK, 1 row affected (0.00 sec)

指定使用某个库

mysql> use db1;

Database changed

创建某个表

mysql> create table t1(`id` int(4),`name` char(40));

Query OK, 0 rows affected (0.05 sec)

插入数据

mysql> insert into t1(id,name) values (1,'gyt');

Query OK, 1 row affected (0.00 sec)

查询表内容

mysql> select * from t1;

+------+------+

| id | name |

+------+------+

| 1 | gyt |

+------+------+

1 row in set (0.00 sec)

查看数据库版本

mysql> select version();

+------------+

| version() |

+------------+

| 5.1.73-log |

+------------+

1 row in set (0.00 sec)

查看mysql状态

mysql> show status;

+-----------------------------------+----------+

| Variable_name | Value |

+-----------------------------------+----------+

| Aborted_clients | 0 |

| Aborted_connects | 3 |

| Binlog_cache_disk_use | 0 |

| Binlog_cache_use | 0 |

| Bytes_received | 870 |

| Bytes_sent | 12055 |

| Com_admin_commands | 0 |

| Com_assign_to_keycache | 0 |

| Com_alter_db | 0 |

| Com_alter_db_upgrade | 0 |

| Com_alter_event | 0 |

| Com_alter_function | 0 |

| Com_alter_procedure | 0 |

| Com_alter_server | 0 |

| Com_alter_table | 0 |

| Com_alter_tablespace | 0 |

| Com_analyze | 0 |

| Com_backup_table | 0 |

| Com_begin | 0 |

| Com_binlog | 0 |

| Com_call_procedure | 0 |

| Com_change_db | 3 |

| Com_change_master | 0 |

| Com_check | 0 |

| Com_checksum | 0 |

| Com_commit | 0 |

| Com_create_db | 1 |

| Com_create_event | 0 |

| Com_create_function | 0 |

| Com_create_index | 0 |

| Com_create_procedure | 0 |

| Com_create_server | 0 |

| Com_create_table | 1 |

| Com_create_trigger | 0 |

| Com_create_udf | 0 |

| Com_create_user | 0 |

| Com_create_view | 0 |

| Com_dealloc_sql | 0 |

| Com_delete | 0 |

| Com_delete_multi | 0 |

| Com_do | 0 |

| Com_drop_db | 0 |

| Com_drop_event | 0 |

| Com_drop_function | 0 |

| Com_drop_index | 0 |

| Com_drop_procedure | 0 |

| Com_drop_server | 0 |

| Com_drop_table | 0 |

| Com_drop_trigger | 0 |

| Com_drop_user | 0 |

| Com_drop_view | 0 |

| Com_empty_query | 0 |

| Com_execute_sql | 0 |

| Com_flush | 0 |

| Com_grant | 0 |

| Com_ha_close | 0 |

| Com_ha_open | 0 |

| Com_ha_read | 0 |

| Com_help | 0 |

| Com_insert | 1 |

| Com_insert_select | 0 |

| Com_install_plugin | 0 |

| Com_kill | 0 |

| Com_load | 0 |

| Com_load_master_data | 0 |

| Com_load_master_table | 0 |

| Com_lock_tables | 0 |

| Com_optimize | 0 |

| Com_preload_keys | 0 |

| Com_prepare_sql | 0 |

| Com_purge | 0 |

| Com_purge_before_date | 0 |

| Com_release_savepoint | 0 |

| Com_rename_table | 0 |

| Com_rename_user | 0 |

| Com_repair | 0 |

| Com_replace | 0 |

| Com_replace_select | 0 |

| Com_reset | 0 |

| Com_restore_table | 0 |

| Com_revoke | 0 |

| Com_revoke_all | 0 |

| Com_rollback | 0 |

| Com_rollback_to_savepoint | 0 |

| Com_savepoint | 0 |

| Com_select | 9 |

| Com_set_option | 0 |

| Com_show_authors | 0 |

| Com_show_binlog_events | 0 |

| Com_show_binlogs | 0 |

| Com_show_charsets | 0 |

| Com_show_collations | 0 |

| Com_show_column_types | 0 |

| Com_show_contributors | 0 |

| Com_show_create_db | 2 |

| Com_show_create_event | 0 |

| Com_show_create_func | 0 |

| Com_show_create_proc | 0 |

| Com_show_create_table | 1 |

| Com_show_create_trigger | 0 |

| Com_show_databases | 2 |

| Com_show_engine_logs | 0 |

| Com_show_engine_mutex | 0 |

| Com_show_engine_status | 0 |

| Com_show_events | 0 |

| Com_show_errors | 0 |

| Com_show_fields | 2 |

| Com_show_function_status | 0 |

| Com_show_grants | 0 |

| Com_show_keys | 0 |

| Com_show_master_status | 0 |

| Com_show_new_master | 0 |

| Com_show_open_tables | 0 |

| Com_show_plugins | 0 |

| Com_show_privileges | 0 |

| Com_show_procedure_status | 0 |

| Com_show_processlist | 0 |

| Com_show_profile | 0 |

| Com_show_profiles | 0 |

| Com_show_slave_hosts | 0 |

| Com_show_slave_status | 0 |

| Com_show_status | 2 |

| Com_show_storage_engines | 0 |

| Com_show_table_status | 0 |

| Com_show_tables | 2 |

| Com_show_triggers | 0 |

| Com_show_variables | 0 |

| Com_show_warnings | 0 |

| Com_slave_start | 0 |

| Com_slave_stop | 0 |

| Com_stmt_close | 0 |

| Com_stmt_execute | 0 |

| Com_stmt_fetch | 0 |

| Com_stmt_prepare | 0 |

| Com_stmt_reprepare | 0 |

| Com_stmt_reset | 0 |

| Com_stmt_send_long_data | 0 |

| Com_truncate | 0 |

| Com_uninstall_plugin | 0 |

| Com_unlock_tables | 0 |

| Com_update | 0 |

| Com_update_multi | 0 |

| Com_xa_commit | 0 |

| Com_xa_end | 0 |

| Com_xa_prepare | 0 |

| Com_xa_recover | 0 |

| Com_xa_rollback | 0 |

| Com_xa_start | 0 |

| Compression | OFF |

| Connections | 6 |

| Created_tmp_disk_tables | 4 |

| Created_tmp_files | 5 |

| Created_tmp_tables | 8 |

| Delayed_errors | 0 |

| Delayed_insert_threads | 0 |

| Delayed_writes | 0 |

| Flush_commands | 1 |

| Handler_commit | 0 |

| Handler_delete | 0 |

| Handler_discover | 0 |

| Handler_prepare | 0 |

| Handler_read_first | 1 |

| Handler_read_key | 0 |

| Handler_read_next | 0 |

| Handler_read_prev | 0 |

| Handler_read_rnd | 0 |

| Handler_read_rnd_next | 55 |

| Handler_rollback | 0 |

| Handler_savepoint | 0 |

| Handler_savepoint_rollback | 0 |

| Handler_update | 0 |

| Handler_write | 49 |

| Innodb_buffer_pool_pages_data | 19 |

| Innodb_buffer_pool_pages_dirty | 0 |

| Innodb_buffer_pool_pages_flushed | 0 |

| Innodb_buffer_pool_pages_free | 493 |

| Innodb_buffer_pool_pages_misc | 0 |

| Innodb_buffer_pool_pages_total | 512 |

| Innodb_buffer_pool_read_ahead_rnd | 1 |

| Innodb_buffer_pool_read_ahead_seq | 0 |

| Innodb_buffer_pool_read_requests | 77 |

| Innodb_buffer_pool_reads | 12 |

| Innodb_buffer_pool_wait_free | 0 |

| Innodb_buffer_pool_write_requests | 0 |

| Innodb_data_fsyncs | 3 |

| Innodb_data_pending_fsyncs | 0 |

| Innodb_data_pending_reads | 0 |

| Innodb_data_pending_writes | 0 |

| Innodb_data_read | 2494464 |

| Innodb_data_reads | 25 |

| Innodb_data_writes | 3 |

| Innodb_data_written | 1536 |

| Innodb_dblwr_pages_written | 0 |

| Innodb_dblwr_writes | 0 |

| Innodb_log_waits | 0 |

| Innodb_log_write_requests | 0 |

| Innodb_log_writes | 1 |

| Innodb_os_log_fsyncs | 3 |

| Innodb_os_log_pending_fsyncs | 0 |

| Innodb_os_log_pending_writes | 0 |

| Innodb_os_log_written | 512 |

| Innodb_page_size | 16384 |

| Innodb_pages_created | 0 |

| Innodb_pages_read | 19 |

| Innodb_pages_written | 0 |

| Innodb_row_lock_current_waits | 0 |

| Innodb_row_lock_time | 0 |

| Innodb_row_lock_time_avg | 0 |

| Innodb_row_lock_time_max | 0 |

| Innodb_row_lock_waits | 0 |

| Innodb_rows_deleted | 0 |

| Innodb_rows_inserted | 0 |

| Innodb_rows_read | 0 |

| Innodb_rows_updated | 0 |

| Key_blocks_not_flushed | 0 |

| Key_blocks_unused | 214342 |

| Key_blocks_used | 0 |

| Key_read_requests | 0 |

| Key_reads | 0 |

| Key_write_requests | 0 |

| Key_writes | 0 |

| Last_query_cost | 0.000000 |

| Max_used_connections | 1 |

| Not_flushed_delayed_rows | 0 |

| Open_files | 20 |

| Open_streams | 0 |

| Open_table_definitions | 16 |

| Open_tables | 9 |

| Opened_files | 87 |

| Opened_table_definitions | 2 |

| Opened_tables | 2 |

| Prepared_stmt_count | 0 |

| Qcache_free_blocks | 1 |

| Qcache_free_memory | 16758168 |

| Qcache_hits | 0 |

| Qcache_inserts | 1 |

| Qcache_lowmem_prunes | 0 |

| Qcache_not_cached | 9 |

| Qcache_queries_in_cache | 1 |

| Qcache_total_blocks | 4 |

| Queries | 34 |

| Questions | 32 |

| Rpl_status | NULL |

| Select_full_join | 0 |

| Select_full_range_join | 0 |

| Select_range | 0 |

| Select_range_check | 0 |

| Select_scan | 6 |

| Slave_open_temp_tables | 0 |

| Slave_retried_transactions | 0 |

| Slave_running | OFF |

| Slow_launch_threads | 0 |

| Slow_queries | 0 |

| Sort_merge_passes | 0 |

| Sort_range | 0 |

| Sort_rows | 0 |

| Sort_scan | 0 |

| Ssl_accept_renegotiates | 0 |

| Ssl_accepts | 0 |

| Ssl_callback_cache_hits | 0 |

| Ssl_cipher | |

| Ssl_cipher_list | |

| Ssl_client_connects | 0 |

| Ssl_connect_renegotiates | 0 |

| Ssl_ctx_verify_depth | 0 |

| Ssl_ctx_verify_mode | 0 |

| Ssl_default_timeout | 0 |

| Ssl_finished_accepts | 0 |

| Ssl_finished_connects | 0 |

| Ssl_session_cache_hits | 0 |

| Ssl_session_cache_misses | 0 |

| Ssl_session_cache_mode | NONE |

| Ssl_session_cache_overflows | 0 |

| Ssl_session_cache_size | 0 |

| Ssl_session_cache_timeouts | 0 |

| Ssl_sessions_reused | 0 |

| Ssl_used_session_cache_entries | 0 |

| Ssl_verify_depth | 0 |

| Ssl_verify_mode | 0 |

| Ssl_version | |

| Table_locks_immediate | 20 |

| Table_locks_waited | 0 |

| Tc_log_max_pages_used | 0 |

| Tc_log_page_size | 0 |

| Tc_log_page_waits | 0 |

| Threads_cached | 0 |

| Threads_connected | 1 |

| Threads_created | 1 |

| Threads_running | 1 |

| Uptime | 1342 |

| Uptime_since_flush_status | 1342 |

+-----------------------------------+----------+

291 rows in set (0.00 sec)

查询mysql参数

mysql> show variables like 'max_connect%';

+--------------------+-------+

| Variable_name | Value |

+--------------------+-------+

| max_connect_errors | 10 |

| max_connections | 151 |

+--------------------+-------+

2 rows in set (0.00 sec)

修改mysql参数

mysql> set global max_connect_errors = 1000;

Query OK, 0 rows affected (0.00 sec)

查看mysql队列

mysql> show processlist;

+----+------+-----------+------+---------+------+-------+------------------+

+----+------+-----------+------+---------+------+-------+------------------+

+----+------+-----------+------+---------+------+-------+------------------+

1 row in set (0.00 sec)

创建普通用户并授权

mysql> grant all on *.* to user1 identified by '123456';

Query OK, 0 rows affected (0.00 sec)

mysql> grant all on db1.* to 'user2'@'10.0.2.100' identified by '111222';

Query OK, 0 rows affected (0.00 sec)

mysql> grant all on db1.* to 'user3'@'%' identified by '231222';

Query OK, 0 rows affected (0.00 sec)

mysql>

更改密码

mysql> update mysql.user set password=PASSWORD("root1234") where user='user1';

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

查询user数量

mysql> select count(*) from mysql.user;

+----------+

| count(*) |

+----------+

| 5 |

+----------+

1 row in set (0.00 sec)

查询user权限

mysql> select * from mysql.dbG;

*************************** 1. row ***************************

Host: %

Db: test

User:

Select_priv: Y

Insert_priv: Y

Update_priv: Y

Delete_priv: Y

Create_priv: Y

Drop_priv: Y

Grant_priv: N

References_priv: Y

Index_priv: Y

Alter_priv: Y

Create_tmp_table_priv: Y

Lock_tables_priv: Y

Create_view_priv: Y

Show_view_priv: Y

Create_routine_priv: Y

Alter_routine_priv: N

Execute_priv: N

Event_priv: Y

Trigger_priv: Y

*************************** 2. row ***************************

Host: %

Db: test\_%

User:

Select_priv: Y

Insert_priv: Y

Update_priv: Y

Delete_priv: Y

Create_priv: Y

Drop_priv: Y

Grant_priv: N

References_priv: Y

Index_priv: Y

Alter_priv: Y

Create_tmp_table_priv: Y

Lock_tables_priv: Y

Create_view_priv: Y

Show_view_priv: Y

Create_routine_priv: Y

Alter_routine_priv: N

Execute_priv: N

Event_priv: Y

Trigger_priv: Y

*************************** 3. row ***************************

Host: localhost

Db: discuz

User: gyt

Select_priv: Y

Insert_priv: Y

Update_priv: Y

Delete_priv: Y

Create_priv: Y

Drop_priv: Y

Grant_priv: N

References_priv: Y

Index_priv: Y

Alter_priv: Y

Create_tmp_table_priv: Y

Lock_tables_priv: Y

Create_view_priv: Y

Show_view_priv: Y

Create_routine_priv: Y

Alter_routine_priv: Y

Execute_priv: Y

Event_priv: Y

Trigger_priv: Y

*************************** 4. row ***************************

Host: 10.0.2.100

Db: db1

User: user2

Select_priv: Y

Insert_priv: Y

Update_priv: Y

Delete_priv: Y

Create_priv: Y

Drop_priv: Y

Grant_priv: N

References_priv: Y

Index_priv: Y

Alter_priv: Y

Create_tmp_table_priv: Y

Lock_tables_priv: Y

Create_view_priv: Y

Show_view_priv: Y

Create_routine_priv: Y

Alter_routine_priv: Y

Execute_priv: Y

Event_priv: Y

Trigger_priv: Y

*************************** 5. row ***************************

Host: %

Db: db1

User: user3

Select_priv: Y

Insert_priv: Y

Update_priv: Y

Delete_priv: Y

Create_priv: Y

Drop_priv: Y

Grant_priv: N

References_priv: Y

Index_priv: Y

Alter_priv: Y

Create_tmp_table_priv: Y

Lock_tables_priv: Y

Create_view_priv: Y

Show_view_priv: Y

Create_routine_priv: Y

Alter_routine_priv: Y

Execute_priv: Y

Event_priv: Y

Trigger_priv: Y

5 rows in set (0.00 sec)

ERROR:

No query specified

模糊匹配

mysql> select * from mysql.db where host like '10.0.%'G;

*************************** 1. row ***************************

Host: 10.0.2.100

Db: db1

User: user2

Select_priv: Y

Insert_priv: Y

Update_priv: Y

Delete_priv: Y

Create_priv: Y

Drop_priv: Y

Grant_priv: N

References_priv: Y

Index_priv: Y

Alter_priv: Y

Create_tmp_table_priv: Y

Lock_tables_priv: Y

Create_view_priv: Y

Show_view_priv: Y

Create_routine_priv: Y

Alter_routine_priv: Y

Execute_priv: Y

Event_priv: Y

Trigger_priv: Y

1 row in set (0.00 sec)

ERROR:

No query specified

mysql>

更新表数据

mysql> update db1.t1 set name='aaa' where id=1;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> use db1;select * from db1;

Database changed

ERROR 1146 (42S02): Table 'db1.db1' doesn't exist

mysql> use db1;select * from t1;

Database changed

+------+------+

| id | name |

+------+------+

| 1 | aaa |

+------+------+

1 row in set (0.00 sec)

mysql>

修复表

mysql> repair table t1[use frm];

shell命令中执行mysql操作

[root@localhost ~]# /usr/local/mysql/bin/mysql -uroot -pmysql1234 mysql -e "show tables"

+---------------------------+

| Tables_in_mysql |

+---------------------------+

| columns_priv |

| db |

| event |

| func |

| general_log |

| help_category |

| help_keyword |

| help_relation |

| help_topic |

| host |

| ndb_binlog_index |

| plugin |

| proc |

| procs_priv |

| servers |

| slow_log |

| tables_priv |

| time_zone |

| time_zone_leap_second |

| time_zone_name |

| time_zone_transition |

| time_zone_transition_type |

| user |

+---------------------------+

[root@localhost ~]#

mysql备份与恢复

备份

[root@localhost ~]# /usr/local/mysql/bin/mysqldump -uroot -p --events mysql >1.sql

Enter password:

[root@localhost ~]# du -sh 1.sql

516K 1.sql

恢复

[root@localhost ~]# /usr/local/mysql/bin/mysqldump -uroot -p --events mysql <1.sql

只备份一个表

[root@localhost ~]# /usr/local/mysql/bin/mysqldump -uroot -p discuz pre_ucenter_vars >2.sql

Enter password:

[root@localhost ~]# du -sh 2.sql

4.0K 2.sql

[root@localhost ~]#

恢复时不需要加表名字

[root@localhost ~]# /usr/local/mysql/bin/mysqldump -uroot -p discuz <2.sql

备份时指定字符集

[root@localhost ~]# /usr/local/mysql/bin/mysqldump -uroot -p --default-character-set=utf8 discuz >1.sql

Enter password:

[root@localhost ~]# du -sh 1.sql

2.3M 1.sql

[root@localhost ~]#

恢复时也指定字符集

[root@localhost ~]# /usr/local/mysql/bin/mysqldump -uroot -p --default-character-set=utf8 discuz <1.sql

mysql主从

介绍

Mysql Replication主要用于时时备份，或者读写分离。

每个从仅可以设置一个主

主在执行sql之后，记录二进制log文件(bin-log)。

从连接主，并从主获取binlog，存于本地relay-log,并从上次记住位置执行sql，一旦遇到错误则停止同步。

主从间的数据库不是实时同步，就算网络连接正常，也存在瞬间，主从数据不一致。

如果主从网络断开，从会在网络正常后，批量同步。

如果对从进行修改数据，那么很可能从在执行主的bin-log时出现错误而停止同步，危险操作。

可以是1主多从，也可以是相互主从（主主）。可以应用在读写分离场景中，用以降低单台mysql服务器的I/O，可以实现mysql服务HA集群。

如果重启master，一定先把slave停掉，执行slave stop命令。然后再重启master的mysql。

步骤

安装步骤参照lamp中mysql步骤。

#在主上创建测试库

/usr/local/mysql/bin/mysql -uroot -S /tmp/mysql.sock -e "create database db1;"

导出主mysql库数据给db1

/usr/local/mysql/bin/mysqldump -uroot -S /tmp/mysql.sock mysql >123.sql

/usr/local/mysql/bin/mysql -uroot -S /tmp/mysql.sock db1 <123.sql

#配置主库

vim /etc/my.cnf

# required unique id between 1 and 2^32 - 1

# defaults to 1 if master-host is not set

# but will not function as a master if omitted

server-id = 1

# Replication Master Server (default)

# binary logging is required for replication

log-bin=mysql-bin

#用来指定需要同步的库，2选1，或指定忽略不同步的库

binlog-do-db=db1

binlog-ignore-db=db1,db2

#修改配置文件后，重启主服务

service mysqld stop

service mysqld start

#主mysql设置root密码

/usr/local/mysql/bin/mysqladmin -u root -S /tmp/mysql.sock password 'root1234'

#进入mysql主 shell

/usr/local/mysql/bin/mysql -u root -S /tmp/mysql.sock -p"root1234"

#授权给从一个用来同步数据的用户rep1

mysql> grant replication slave on *.* to 'repl'@192.168.211.128' identified by 'root1234';

mysql> flush tables with read lock;

#记住前2列内容

mysql>show master status;

#从上设置

vim /etc/my.cnf

# required unique id between 1 and 2^32 - 1

# defaults to 1 if master-host is not set

# but will not function as a master if omitted

server-id = 2

#重启从Mysql服务

service mysqld restart

#从主上拷贝db1库数据到从

scp db1.sql root@192.168.211.128:/root/

#在从上创建db1数据库

/usr/local/mysql/bin/mysql -uroot -S /tmp/mysql.sock -e "create database db1";

#在从上导入db1从库

/usr/local/mysql/bin/mysql -uroot -S /tmp/mysql.sock db1 </root/db1.sql

#登录从mysql

/usr/local/mysql/bin/mysql -uroot -S /tmp/mysql.sock

#停止从库

mysql>slave stop

#在从上设置主库参数，此处master_log_file和master_log_pos为主上show master status看到的前2列值

mysql> change master to master_host='192.168.211.133',master_port=3306,master_user='repl',master_password='root1234',master_log_file='mysql-bin.000004',master_log_pos=555;

Query OK, 0 rows affected (0.03 sec)

#在从上启动数据库

mysql> slave start;

Query OK, 0 rows affected (0.00 sec)

#主上执行unlock read

/usr/local/mysql/bin/mysql -uroot -S /tmp/mysql.sock -proot1234 -e "unlock tables"

#从上查看状态，有这2个Yes，算正常

mysql> show slave statusG;

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

测试

#主上清空db1K库db表

mysql> use db1;

Database changed

mysql> select count(*) from db;

+----------+

| count(*) |

+----------+

| 2 |

+----------+

1 row in set (0.00 sec)

mysql> truncate table db;

Query OK, 0 rows affected (0.00 sec)

#进入从，确认为0，说明同步

mysql> use db1;

Database changed

mysql> select count(*) from db;

+----------+

| count(*) |

+----------+

| 0 |

+----------+

1 row in set (0.00 sec)

#主上删除表db

mysql> select count(*) from db;

ERROR 1146 (42S02): Table 'db1.db' doesn't exist

#在从上db表不存在了

mysql> drop table db;

Query OK, 0 rows affected (0.00 sec)