目前分類:資料庫 (2)

瀏覽方式: 標題列表 簡短摘要

家中有一台synology nas  內建的是 MariaDB 5.5.43 (mysql 分支)
另外有一fedora linux 的MySQL 5.1  
最近嘗試將兩部MySQL 表兄弟做replication,以下為實戰筆記....

區分以下段落
0.replication概要
1.環境說明
2.設定mater
3.設定slave
4.異常處理

0.mysql replication 官方說法
參考: http://dev.mysql.com/doc/refman/5.0/en/replication...

0.1 mysql的replication 單向複寫
可設定一個master,多個slave,靠啟用binary log來將異動資料由master送給slave
 

0.2 Scale out ,分散讀寫不同作業,提高效能

 

0.3 master異常時,切換到slave維持運作
mysql>change master to ...   可將master 指到的slave1
正常情況
 

異常時切換master
 



1.環境說明
master: MySQL 5.1 on fedora linux , ip=master.mydomain.com
slave : MariaDB 5.5 on Synology Nas (mysql 分支) , dns=slave.mydomain.com ,ip=slave_db_ip_address

2.設定master 
2.1.edit my.cnf@master db   (/etc/my.cnf)
#master config
[mysqld]
server-id=9419601
log_bin=mysql-bin
log_error=mysql-bin.err
binlog_do_db=db1
binlog_do_db=db2
binlog_do_db=db3

2.2 create replication user @master db

CREATE USER 'rep_user'@'slave_db_ip_address' IDENTIFIED BY  'rep_user_password';

GRANT REPLICATION SLAVE , REPLICATION CLIENT ON * . * TO  'rep_user'@'slave_db_ip_address' IDENTIFIED BY  'rep_user_password';

2.3 restart@master db

2.4 顯示master狀態@master db
mysql>show master status;

變數
File mysql-bin.000009
Position 11724
Binlog_Do_DB db1,db2,db3
Binlog_Ignore_DB

2.5 顯示目前連線之slave host@master db
mysql>show slave hosts;

Server_id Host Port Rpl_recovery_rank Master_id
1444825616 slave.mydomain.com 3306 0 9419601


3.設定slave

3.1 edit  my.cnf@slave db   (/etc/mysql/my.cnf)

[mysqld]
server-id=1444822168                #由phpMyadmin之Replication頁籤/slave 取得
report-host=slave.mydomain.com  #此hostname 將回報給master 
slave-skip-errors = all                 #避免發生錯誤時,中斷replication

3.2 restart@slave db
/usr/syno/bin/synopkg restart MariaDB 

3.3 設定mater@slave db

CHANGE MASTER TO

  MASTER_HOST='master.mydomain.com',
  MASTER_USER='rep_user',

  MASTER_PASSWORD='rep_user_password',
 MASTER_PORT=3306

3.4 顯示slave 狀態@slave db
mysql>show slave status;

變數
Slave_IO_State Waiting for master to send event
Master_Host master.myadomain.com
Master_User rep_user
Master_Port 3306
Connect_Retry 60
Master_Log_File mysql-bin.000009
Read_Master_Log_Pos 11450
Relay_Log_File mysqld-relay-bin.000013
Relay_Log_Pos 8157
Relay_Master_Log_File mysql-bin.000009
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 11450
Relay_Log_Space 8452
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


4.replication異常停止 處理方法
4.1 顯示master 狀態@master db
mysql>show master status;
紀錄File 及Position

變數
File mysql-bin.000009
Position 11724


4.2 重設master_log_file 及master_log_pos @slave db
mysql>change master to 
master_host='master.mydomain.com',
 master_user='rep_user', 
 master_password='rep_user_password', 
 master_port=3306, 
 master_log_file='mysql-bin.000009', 
 master_log_pos=11724 ;






  





michaelfang 發表在 痞客邦 PIXNET 留言(0) 人氣()

文章出處: http://www.enterpriseappstoday.com/data-management/10-database-design-best-practices.html



Posted July 15, 2014By Ann All     Feedback

A well-designed database makes it easier to derive value from enterprise data. We asked three experts for their top database design tips.

With enterprises looking for new ways to use data to gain competitive advantage, database design is now more important than ever. Here are some best practices that will result in a well-designed database.

Focus on the data, not the application

"Applications come and go, but data is forever," said Justin Cave, lead database consultant at Distributed Database Consulting, noting that he frequently sees programmers designing a database to cater to a specific application without considering future data needs. While they are often under pressure to deliver a database in a relatively short period of time, Cave said taking design shortcuts to help meet a deadline can make it much tougher to add new functionality in the future.

"If you properly design a database upfront, it is going to work for everybody. If you skip some functionality because 'we don't really need that,' you can count on someone needing it in the future," he said.

Leverage the power of your database

Too many programmers "treat databases like a data dump or a black box," Cave said, and choose to build database-independent applications. While there are instances where this is appropriate, it generally is not the best approach. "When you are not using the power of the database, you end up making things less efficient and harder to maintain," he said.

Utilizing the database makes it relatively easy to reuse design elements and add functionality. "But if everything is built into the application, you'll have to rebuild validation and a lot of the logic and you'll never get the same information," he said.

Include DBAs in the design phase

"There are so many ways that data gets used, it's hard for someone whose task is to 'build X' to envision that," Cave said, adding that it's not uncommon for developers to wait until the final stages of development to interact with database administrators. The result may be a lack of attention to details important to DBAs, such as naming conventions which make it easy to identify objects contained in the database. Given that DBAs may not have much time to spare to work with developers, Cave suggested it's a good idea for developers to "learn to think like a DBA."

Use data models

Despite widespread agreement that software engineering is important,many programmers forego data models, said Michael Blaha, a partner at Modelsoft Consulting and author of seven books including "Object-Oriented Modeling and Design." Noting that "data is the memory of an enterprise," he said, "Code is important, but replaceable -- an organization can always rewrite an app or purchase a new app. There is no excuse for not constructing at least a summary data model."

Consider different data interaction strategies

Many developers lack imagination in how applications can interact with a database, Blaha said. They may choose to use a database layer, hiding the database with programming objects that make database data indistinguishable from in-memory data. While this is a viable strategy, it has limitations.

"There are additional data interaction strategies," Blaha said. "For example, dedicated methods can encapsulate data access. Some methods can be written as stored procedures. Others can be written in programming code. The remaining programming logic accesses the database via these special methods."

Mobile Customer Care: It's More than Hype
 

Reuse good ideas

Rather than "obsessing on use cases" and trying to capture requirements from scratch, Blaha said developers can reuse ideas that have worked well in the past.

"For example, I often reverse engineer databases -- databases of an application to be replaced as well as databases of related applications," he said. "These existing databases often do not have an available data model. But a data model is implicit in the database schema and can be at least partially extracted with database reverse engineering techniques."

Another good example of reuse is incorporating data patterns, Blaha said, noting that one of his books, "Patterns of Data Modeling," explains many data patterns. "There are tried-and-true data representations that often occur and need not be recreated from scratch."

Index foreign database keys

Every foreign key in a database should be covered by an index, Blaha said. While some foreign keys are covered by implicit indexes that are created as a side effect of primary and alternate key definitions, developers should create an explicit index for the other foreign keys, he explained.

"Database queries often traverse from a foreign key to its primary key," Blaha said. "The resulting joins are efficient if primary keys are defined and every foreign key has an index. Just one missing index can degrade query performance by several orders of magnitude. It's hard to predict the traversals that might occur."

Pick database keys that support partitioning

Even if you're not planning on partitioning now, the application will likely need to evolve to support it at some point. Bryn Rhodes, co-owner and software architect for Database Consulting Group, said selection of a key type that easily supports partitioning can mean the difference between a smooth transition and a difficult upgrade.

Model with multiple perspectives

Optimal structures for one use case are often different than for another, especially when considering access paths to data. Make sure you take multiple perspectives into account so you find these potential issues early, Rhodes said, and build structures that enable data access in various ways.

Don't ignore the data access layer

When modeling the structures, be sure to consider that everything you build is going to have to be consumed by a data access layer at some point. Avoid using proprietary extensions to SQL unless there is a very good reason to do so. And if you must use proprietary extensions, try to provide some insulation for them.

Ann All is the editor of Enterprise Apps Today and eSecurity Planet. She has covered business and technology for more than a decade, writing about everything from business intelligence to virtualization.

michaelfang 發表在 痞客邦 PIXNET 留言(0) 人氣()