modules/replication/replication.module

<?php
/* $Id$ */

/**
 * Replication plugin for multi-master Mysql Replication
 * Author: Michael Heath
 * Date: 2007-06-13 17:46 GMT
 * Developed for use at eServGlobal Ltd.
 * http://www.eservglobal.com
 */

function replication_help($section='') {

  $output = '';

  switch ($section) {
    case "admin/help#replication":
      $output = '<p>'.  t("Database Replication Tool"). '</p>';
      break;
  }

  return $output;
} // function replication_help


function replication_perm() {

  return array('administer replication');

} // function replication_perm


function replication_menu() {

  $items = array();

  // Administration Options
  $items[] = array(
    'path' => 'admin/settings/replication',
    'title' => t('Replication settings'),
    'callback' => 'drupal_get_form',
    'callback arguments' => 'replication_admin',
    'access' => user_access('administer replication'),
    'description' => ('Configure database replication'),
    'type' => MENU_NORMAL_ITEM,
   );
  $items[] = array(
    'path' => 'admin/settings/replication/refresh',
    'title' => t('Refresh replication'),
    'callback' => 'drupal_get_form',
    'callback arguments' => 'replication_adminrefresh',
    'access' => user_access('administer replication'),
    'description' => ('Configure database replication'),
    'type' => MENU_NORMAL_ITEM,
   );
  $items[] = array(
    'path' => 'admin/settings/replication/adminmysql',
    'title' => t('Mysql access'),
    'callback' => 'drupal_get_form',
    'callback arguments' => 'replication_adminmysql',
    'access' => user_access('administer replication'),
    'description' => ('Configure database replication'),
    'type' => MENU_NORMAL_ITEM,
   );

  return $items;
} // function replication_menu

function replication_adminmysql() {

  $form['replication_mysqladmin_item_title'] = array(
    '#type' => 'item',
    '#title' => t('Note'),
    '#value' => t('Providing this information enables drupal to switch masters via the web interface.  This is potentially dangerous.  Please ensure you know what you are doing and backup your database before proceding!'),
  );
  $form['replication_mysqladmin_host'] = array(
    '#type' => 'textfield',
    '#title' => t('Mysql server host'),
    '#default_value' => local_variable_get('replication_mysqladmin_host', 'localhost'),
    '#size' => 64,
    '#maxlength' => 64,
    '#required' => 'TRUE',
    '#description' => t("The value of mysql master host.")
  );
  $form['replication_mysqladmin_port'] = array(
    '#type' => 'textfield',
    '#title' => t('Mysql listening port'),
    '#default_value' => local_variable_get('replication_mysqladmin_port', '3306'),
    '#size' => 5,
    '#maxlength' => 5,
    '#required' => 'TRUE',
    '#description' => t("The port number that the specified server is listening on.")
  );
  $form['replication_mysqladmin_user'] = array(
    '#type' => 'textfield',
    '#title' => t('Mysql username'),
    '#default_value' => local_variable_get('replication_mysqladmin_user', 'root'),
    '#size' => 64,
    '#maxlength' => 64,
    '#required' => 'TRUE',
    '#description' => t("Enter the replication username required for this master.")
  );
  $form['replication_mysqladmin_pass'] = array(
    '#type' => 'password',
    '#title' => t('Mysql superuser password'),
    '#size' => 64,
    '#maxlength' => 64,
    '#description' => t("Enter the superuser password.  This field is optional - only specify the password if you really mean to.")
  );

  return local_system_settings_form($form);
} //function replication_adminmysql


function replication_admin() {

  $form['master'] = array (
    '#type' => 'fieldset', 
    '#title' => t('Master configuration'), 
    '#weight' => 0, 
    '#collapsible' => TRUE, 
    '#collapsed' => FALSE,
  );
  $form['sequences'] = array (
    '#type' => 'fieldset', 
    '#title' => t('Sequence settings'), 
    '#weight' => 0, 
    '#collapsible' => TRUE, 
    '#collapsed' => TRUE,
  );
  $form['master']['replication_admin_masterhost'] = array(
    '#type' => 'textfield',
    '#title' => t('Mysql master host'),
    '#default_value' => local_variable_get('replication_admin_masterhost', 'somehost.somedomain'),
    '#size' => 64,
    '#maxlength' => 64,
    '#required' => 'TRUE',
    '#description' => t("The value of mysql master host.")
  );
  $form['master']['replication_admin_masterport'] = array(
    '#type' => 'textfield',
    '#title' => t('Mysql master listening port'),
    '#default_value' => local_variable_get('replication_admin_masterport', '3306'),
    '#size' => 5,
    '#maxlength' => 5,
    '#required' => 'TRUE',
    '#description' => t("The port number that the master specified is listening on.")
  );
  $form['master']['replication_admin_masteruser'] = array(
    '#type' => 'textfield',
    '#title' => t('Mysql master username'),
    '#default_value' => local_variable_get('replication_admin_masteruser', 'someuser'),
    '#size' => 64,
    '#maxlength' => 64,
    '#required' => 'TRUE',
    '#description' => t("Enter the replication username required for this master.")
  );
  $form['master']['replication_admin_masterpass'] = array(
    '#type' => 'password',
    '#title' => t('Mysql master password'),
    '#size' => 64,
    '#maxlength' => 64,
    '#required' => 'TRUE',
    '#description' => t("Enter the replication password required for this master.")
  );
  $form['sequences']['replication_admin_item_title'] = array(
    '#type' => 'item',
    '#title' => t('Note'),
    '#value' => t('Playing with sequence numbers is dangerous.  Please ensure you know what you are doing and backup your database before proceding!'),
  );
  $form['sequences']['replication_admin_increment'] = array(
    '#type' => 'textfield',
    '#title' => t('Mysql increment value'),
    '#default_value' => local_variable_get('replication_admin_increment', '1'),
    '#size' => 4,
    '#maxlength' => 4,
    '#required' => 'TRUE',
    '#description' => t("The value of mysql auto_increment_increment.")
  );
  $form['sequences']['replication_admin_offset'] = array(
    '#type' => 'textfield',
    '#title' => t('Mysql increment offset value'),
    '#default_value' => local_variable_get('replication_admin_offset', '1'),
    '#size' => 4,
    '#maxlength' => 4,
    '#required' => 'TRUE',
    '#description' => t("The value of mysql auto_increment_offset.")
  );
  return local_system_settings_form($form);
} //function replication_admin


function replication_adminrefresh() {
  
  $form['replication_adminrefresh_text'] = array(
    '#type' => 'item',
    '#value' => t('Submitting this form will cause sequence numbers to be re-aligned and activate any changes you have made to the replication settings.')
  );
  $form['replication_adminrefresh_dbrefresh'] = array(
    '#type' => 'submit',
    '#default_value' => 'Refresh replication.'
  );

  return ($form);
} //function replication_adminreset

function replication_adminrefresh_submit($form_id, $form_values) {
  /* select from the sequences table */
  $sql = db_rewrite_sql ('SELECT name,id FROM {sequences}');
  $queryResult = db_query($sql);
    
  while ($links = db_fetch_object($queryResult)) {
    // VAL + increment-1 intdivide 10 * 10 +offset
    $newid =  ((intval (($links->id + ( local_variable_get('replication_admin_increment', '1') -1 ) ) / 10) * 10 ) + local_variable_get('replication_admin_offset', '1') -1 );
    msg_r ($links->id.' -> '.$newid);
    db_query ("UPDATE {sequences} set id=%d where name='%s'", $newid, $links->name);
  }

  // db_query ("LOAD data from MASTER");

  if ($dbconnection = mysql_connect ( local_variable_get('replication_mysqladmin_host','localhost'), local_variable_get('replication_mysqladmin_user','root'), local_variable_get('replication_mysqladmin_pass','pass'))) {
    drupal_set_message('Superuser connection: OK');
    $sqlQuery[0] = ("STOP slave;");
    $sqlQuery[1] = ("grant replication slave on *.* to '".local_variable_get('replication_admin_masteruser', 'someuser')."'@".gethostbyname(local_variable_get('replication_admin_masterhost', 'somehost.somedomain'))." identified by '".local_variable_get('replication_admin_masterpass', 'somepass')."'");
    $sqlQuery[2] = ("grant reload,process,super,replication client on *.* to '".local_variable_get('replication_admin_masteruser', 'someuser')."'@".gethostbyname(local_variable_get('replication_admin_masterhost', 'somehost.somedomain'))." identified by '".local_variable_get('replication_admin_masterpass', 'somepass')."'");
    $sqlQuery[3] = ("CHANGE MASTER TO MASTER_HOST='". local_variable_get('replication_admin_masterhost', 'somehost.somedomain') ."', MASTER_PORT=". local_variable_get('replication_admin_masterport', '3306') .", MASTER_USER='". local_variable_get('replication_admin_masteruser', 'someuser') ."', MASTER_PASSWORD='". local_variable_get('replication_admin_masterpass', 'somepass') ."';");
    $sqlQuery[4] = ("LOAD data from MASTER;");
    $sqlQuery[5] = ("START slave;");
    $loopCounter = 0;
    while ($loopCounter < 5) {
      //$result = mysql_query ($sqlQuery[$loopCounter]);
      $drupalmessage .= ($sqlQuery[$loopCounter]);
      if (!$result) $drupalmessage .= mysql_error();
      $loopCounter++;
    }
    $drupalmessage .= "<li>Database updated.";
  } else {
    $drupalmessage .= ('Unable to connect as superuser!');
  }

  $drupalmessage .= ('<li>Sequence numbers have been re-aligned.');

  drupal_set_message($drupalmessage);

  drupal_get_form('replication_admin');
} //function replication_adminrefresh_submit
1	<?php
2	/* $Id$ */
3
4	/**
5	* Replication plugin for multi-master Mysql Replication
6	* Author: Michael Heath
7	* Date: 2007-06-13 17:46 GMT
8	* Developed for use at eServGlobal Ltd.
9	* http://www.eservglobal.com
10	*/
11
12	function replication_help($section='') {
13
14	$output = '';
15
16	switch ($section) {
17	case "admin/help#replication":
18	$output = '<p>'. t("Database Replication Tool"). '</p>';
19	break;
20	}
21
22	return $output;
23	} // function replication_help
24
25
26	function replication_perm() {
27
28	return array('administer replication');
29
30	} // function replication_perm
31
32
33	function replication_menu() {
34
35	$items = array();
36
37	// Administration Options
38	$items[] = array(
39	'path' => 'admin/settings/replication',
40	'title' => t('Replication settings'),
41	'callback' => 'drupal_get_form',
42	'callback arguments' => 'replication_admin',
43	'access' => user_access('administer replication'),
44	'description' => ('Configure database replication'),
45	'type' => MENU_NORMAL_ITEM,
46	);
47	$items[] = array(
48	'path' => 'admin/settings/replication/refresh',
49	'title' => t('Refresh replication'),
50	'callback' => 'drupal_get_form',
51	'callback arguments' => 'replication_adminrefresh',
52	'access' => user_access('administer replication'),
53	'description' => ('Configure database replication'),
54	'type' => MENU_NORMAL_ITEM,
55	);
56	$items[] = array(
57	'path' => 'admin/settings/replication/adminmysql',
58	'title' => t('Mysql access'),
59	'callback' => 'drupal_get_form',
60	'callback arguments' => 'replication_adminmysql',
61	'access' => user_access('administer replication'),
62	'description' => ('Configure database replication'),
63	'type' => MENU_NORMAL_ITEM,
64	);
65
66	return $items;
67	} // function replication_menu
68
69	function replication_adminmysql() {
70
71	$form['replication_mysqladmin_item_title'] = array(
72	'#type' => 'item',
73	'#title' => t('Note'),
74	'#value' => t('Providing this information enables drupal to switch masters via the web interface. This is potentially dangerous. Please ensure you know what you are doing and backup your database before proceding!'),
75	);
76	$form['replication_mysqladmin_host'] = array(
77	'#type' => 'textfield',
78	'#title' => t('Mysql server host'),
79	'#default_value' => local_variable_get('replication_mysqladmin_host', 'localhost'),
80	'#size' => 64,
81	'#maxlength' => 64,
82	'#required' => 'TRUE',
83	'#description' => t("The value of mysql master host.")
84	);
85	$form['replication_mysqladmin_port'] = array(
86	'#type' => 'textfield',
87	'#title' => t('Mysql listening port'),
88	'#default_value' => local_variable_get('replication_mysqladmin_port', '3306'),
89	'#size' => 5,
90	'#maxlength' => 5,
91	'#required' => 'TRUE',
92	'#description' => t("The port number that the specified server is listening on.")
93	);
94	$form['replication_mysqladmin_user'] = array(
95	'#type' => 'textfield',
96	'#title' => t('Mysql username'),
97	'#default_value' => local_variable_get('replication_mysqladmin_user', 'root'),
98	'#size' => 64,
99	'#maxlength' => 64,
100	'#required' => 'TRUE',
101	'#description' => t("Enter the replication username required for this master.")
102	);
103	$form['replication_mysqladmin_pass'] = array(
104	'#type' => 'password',
105	'#title' => t('Mysql superuser password'),
106	'#size' => 64,
107	'#maxlength' => 64,
108	'#description' => t("Enter the superuser password. This field is optional - only specify the password if you really mean to.")
109	);
110
111	return local_system_settings_form($form);
112	} //function replication_adminmysql
113
114
115	function replication_admin() {
116
117	$form['master'] = array (
118	'#type' => 'fieldset',
119	'#title' => t('Master configuration'),
120	'#weight' => 0,
121	'#collapsible' => TRUE,
122	'#collapsed' => FALSE,
123	);
124	$form['sequences'] = array (
125	'#type' => 'fieldset',
126	'#title' => t('Sequence settings'),
127	'#weight' => 0,
128	'#collapsible' => TRUE,
129	'#collapsed' => TRUE,
130	);
131	$form['master']['replication_admin_masterhost'] = array(
132	'#type' => 'textfield',
133	'#title' => t('Mysql master host'),
134	'#default_value' => local_variable_get('replication_admin_masterhost', 'somehost.somedomain'),
135	'#size' => 64,
136	'#maxlength' => 64,
137	'#required' => 'TRUE',
138	'#description' => t("The value of mysql master host.")
139	);
140	$form['master']['replication_admin_masterport'] = array(
141	'#type' => 'textfield',
142	'#title' => t('Mysql master listening port'),
143	'#default_value' => local_variable_get('replication_admin_masterport', '3306'),
144	'#size' => 5,
145	'#maxlength' => 5,
146	'#required' => 'TRUE',
147	'#description' => t("The port number that the master specified is listening on.")
148	);
149	$form['master']['replication_admin_masteruser'] = array(
150	'#type' => 'textfield',
151	'#title' => t('Mysql master username'),
152	'#default_value' => local_variable_get('replication_admin_masteruser', 'someuser'),
153	'#size' => 64,
154	'#maxlength' => 64,
155	'#required' => 'TRUE',
156	'#description' => t("Enter the replication username required for this master.")
157	);
158	$form['master']['replication_admin_masterpass'] = array(
159	'#type' => 'password',
160	'#title' => t('Mysql master password'),
161	'#size' => 64,
162	'#maxlength' => 64,
163	'#required' => 'TRUE',
164	'#description' => t("Enter the replication password required for this master.")
165	);
166	$form['sequences']['replication_admin_item_title'] = array(
167	'#type' => 'item',
168	'#title' => t('Note'),
169	'#value' => t('Playing with sequence numbers is dangerous. Please ensure you know what you are doing and backup your database before proceding!'),
170	);
171	$form['sequences']['replication_admin_increment'] = array(
172	'#type' => 'textfield',
173	'#title' => t('Mysql increment value'),
174	'#default_value' => local_variable_get('replication_admin_increment', '1'),
175	'#size' => 4,
176	'#maxlength' => 4,
177	'#required' => 'TRUE',
178	'#description' => t("The value of mysql auto_increment_increment.")
179	);
180	$form['sequences']['replication_admin_offset'] = array(
181	'#type' => 'textfield',
182	'#title' => t('Mysql increment offset value'),
183	'#default_value' => local_variable_get('replication_admin_offset', '1'),
184	'#size' => 4,
185	'#maxlength' => 4,
186	'#required' => 'TRUE',
187	'#description' => t("The value of mysql auto_increment_offset.")
188	);
189	return local_system_settings_form($form);
190	} //function replication_admin
191
192
193	function replication_adminrefresh() {
194
195	$form['replication_adminrefresh_text'] = array(
196	'#type' => 'item',
197	'#value' => t('Submitting this form will cause sequence numbers to be re-aligned and activate any changes you have made to the replication settings.')
198	);
199	$form['replication_adminrefresh_dbrefresh'] = array(
200	'#type' => 'submit',
201	'#default_value' => 'Refresh replication.'
202	);
203
204	return ($form);
205	} //function replication_adminreset
206
207	function replication_adminrefresh_submit($form_id, $form_values) {
208	/* select from the sequences table */
209	$sql = db_rewrite_sql ('SELECT name,id FROM {sequences}');
210	$queryResult = db_query($sql);
211
212	while ($links = db_fetch_object($queryResult)) {
213	// VAL + increment-1 intdivide 10 * 10 +offset
214	$newid = ((intval (($links->id + ( local_variable_get('replication_admin_increment', '1') -1 ) ) / 10) * 10 ) + local_variable_get('replication_admin_offset', '1') -1 );
215	msg_r ($links->id.' -> '.$newid);
216	db_query ("UPDATE {sequences} set id=%d where name='%s'", $newid, $links->name);
217	}
218
219	// db_query ("LOAD data from MASTER");
220
221	if ($dbconnection = mysql_connect ( local_variable_get('replication_mysqladmin_host','localhost'), local_variable_get('replication_mysqladmin_user','root'), local_variable_get('replication_mysqladmin_pass','pass'))) {
222	drupal_set_message('Superuser connection: OK');
223	$sqlQuery[0] = ("STOP slave;");
224	$sqlQuery[1] = ("grant replication slave on . to '".local_variable_get('replication_admin_masteruser', 'someuser')."'@".gethostbyname(local_variable_get('replication_admin_masterhost', 'somehost.somedomain'))." identified by '".local_variable_get('replication_admin_masterpass', 'somepass')."'");
225	$sqlQuery[2] = ("grant reload,process,super,replication client on . to '".local_variable_get('replication_admin_masteruser', 'someuser')."'@".gethostbyname(local_variable_get('replication_admin_masterhost', 'somehost.somedomain'))." identified by '".local_variable_get('replication_admin_masterpass', 'somepass')."'");
226	$sqlQuery[3] = ("CHANGE MASTER TO MASTER_HOST='". local_variable_get('replication_admin_masterhost', 'somehost.somedomain') ."', MASTER_PORT=". local_variable_get('replication_admin_masterport', '3306') .", MASTER_USER='". local_variable_get('replication_admin_masteruser', 'someuser') ."', MASTER_PASSWORD='". local_variable_get('replication_admin_masterpass', 'somepass') ."';");
227	$sqlQuery[4] = ("LOAD data from MASTER;");
228	$sqlQuery[5] = ("START slave;");
229	$loopCounter = 0;
230	while ($loopCounter < 5) {
231	//$result = mysql_query ($sqlQuery[$loopCounter]);
232	$drupalmessage .= ($sqlQuery[$loopCounter]);
233	if (!$result) $drupalmessage .= mysql_error();
234	$loopCounter++;
235	}
236	$drupalmessage .= "<li>Database updated.";
237	} else {
238	$drupalmessage .= ('Unable to connect as superuser!');
239	}
240
241	$drupalmessage .= ('<li>Sequence numbers have been re-aligned.');
242
243	drupal_set_message($drupalmessage);
244
245	drupal_get_form('replication_admin');
246	} //function replication_adminrefresh_submit