MYSQL :: Mysql Master/Slave Read Write To Database Question

MySQL Master/Slave
In the case of 2 3.23.X series mysql databases in a master/slave
replication setup (myiasm tables), if a FLUSH TABLES WITH READ LOCK is
put on the slave, does the master effectively lock too because it is
waiting for the binlog update(s) to be confirmed from the slave, or
does the master merrily carry on not caring in the least that the
slave tables are locked?

MySQL Replication (Multiple Master & 1 Slave)
Is it possible to replicate 2 or more master server & 1 slave server?

I have tried 1 master & multiple slave running but I want to try 4 master server & 1 slave server. Is this possible?

MySql Write Vs Read
Essentially I am faced with a design choice;

have an oft run (modifiable) query run every time any user wants to
run it, comparing criteria againt each and every user in the table and
delviering list OR saving that list (user/user/) so the next time all
that needs to be queried is the existence of a record in the table.

The basic tradeoff; version 1 there are many simultaneous queries of
the user db on complex search criteria; version 2 there are fewer but
their are as many far more simple queries to a much larger table, not
to mention many writes to same to keep the table current.

I know this is pretty vague, but I was wondering if in general there
is an area that mysql excels in vis a vis complex query smaller tables
simple query larger tables.

Question 2 is there anyway to guestiate the search/write times of a
user log in to both query and update? #Records, fields, etc? I'd like
to keep log in to < 10 seconds max.

Master/Slave Restoration.
I have 2 servers (A and B) setup as Master and Slave respectively.
Upon A's failure, all client's request will be directed to B.

How can I set A to become master again after its been restored from
its failure? In chapter 4.10 of mysql docs, it mentioned (vaguely)
that A will become B's slave to sync all operations that had happened
after the failure. Finally, upon full sync switch A to become Master
again and B to slave.

How can I setup A to be a slave of B? The problem I faced is that I
can't find out what the bin log and position is from B since "show
master status" is always empty. I guess B still thinks its a slave.

How To Change Read/write Permission On Mysql Data Base.
I had a total crash and the server guy gave me all my data in a hand basket.. just kidding..

Actually I was given the data all zipped up and the mysql data bases were separate and they were the direct downloads as follows:
wp_comments.MYD
wp_comments.MYI
wp_comments.frm
all data was in this format.
I used SSH and uploaded it to the proper directory on the server and all is well at least as far as visitors seeing all the data.. but when I log into the site I get the following...

WordPress database error: [Table 'wp_options' is read only]
UPDATE wp_options SET option_value = 'O:9:"magpierss":18:{s:6:"parser";i:0;s:12:"current_item";a:0:{}s:5:"it ems";a:10:{i:0;a:12:{s:5:"title";s:13:"WordPress 2.2";s:4:"link";s:54:"http://wordpress.org/development/2007/05/wordpress-22/

I used SSH and tried to change the concerned files to writable and it works fine but the same error is generated.

I can go direct to the data base using myAdmin and cannot edit (write) to any table. It says the data base is read only.

On the same mysql area, I have another data base that was done via the normal way and I can write to it just fine.

I can find nothing to change to make the site writeable.

Any help here would be greatly appreciated..

SSH to the server and changing the files to writable has no effiect..

Master/slave Setup To Do Replication
using mysql 4.1.7, have a master/slave setup to do replication.

Is there anyway to prevent the slavedb from accepting inserts other than the replication inserts?

If rows do get inserted directly into the slave, there is an error because the two databases no longer match. Is there anyway to recover from this?

MyISAM On Master And InnoDB On Slave Server
Is this possible to have a MyISAM table on master server and change its storage engine (to InnoDB) on slave server in a replication environment?

If this is possible, what should I consider before changing MyISAM to InnoDB on slave server?

Here is the table structure:

Forwarding Insert/Updates From Slave To Master
Is there a way within MySQL to forward all Inserts/Update requests from the
Slave to the Master, or much I rearchitect my application to carry two
connection; one connection to the Master for updates and the other
connection to the slave for Selects?

Procedures To Promote Slave To Master Server
What is the proper procedure for promoting a Slave into a Master
replication server?

We want to do some fail-over testing. When we tried this in the past, the
former master would not replicate. We got an error: "master and slave
have equal MySQL server ids".

The fail-over procedure includes renaming the slave and exchanging IP
addresses with the master server. We did not change the server-ids.

I haven't found any information on promoting slaves to master servers.

Restart Slave After Master Has Died In Replicated Environment
Our master database ran out of connections and we had to restart the
server (couldn't SSH into the box). Rebooting the server worked and
of our clients resumed activity.

The problem is that the slave didn't start automatically so I tried to
do a START SLAVE; on it.

This is the latest shown in the error log on the slave.

040110 20:06:13 Error reading packet from server: binlog truncated in
the middle of event (server_errno=1236)
040110 20:06:13 Got fatal error 1236: 'binlog truncated in the middle
of event' from master when reading data from binary log
040110 20:06:13 Slave I/O thread exiting, read up to log
'user1_master.002', position 802078191

(THIS IS AFTER I DID A SLAVE_START TODAY 1/20)

040120 8:56:39 Slave I/O thread: connected to master
'slave@db1.ezboard.com:5000', replication started in log
'db1_master.002' at position 802078191
040120 8:56:39 Slave: there is an unfinished transaction in the
relay log (could find neither COMMIT nor ROLLBACK in the relay log);
it could be that the master died while writing the transaction to its
binary log. Now the slave is rolling back the transaction. Error_code:
0
040120 8:56:39 Error running query, slave SQL thread aborted. Fix
the problem, and restart the slave SQL thread with "SLAVE START". We
stopped at log 'db1_master.002' position 802078147.

Replication, Slave Thread: Error Connecting To Master: Access Denied For User:
I have MySQL 3.23.41 on a RH-7.2 Linux used as local server. I'm trying to replicate the databases from server (192.168.0.1) to another linux PC (192.168.0.3).

Following the manual, i grant to the master a user with file privileges, and
edit both my.cnf files. The master starts fine but the slave mysql.log shows up an error:

Slave thread: error connecting to master: Access denied for user:
replica@localhost' (Using password: YES) (0), retry in 60 sec

Write Once Read Many Field
I am designing a database and one of the requirements my client has is that once data is entered into certain fields it cannot, under any circumstances, be changed. Is it possible to create a write once read many (WORM) field in MySQL directly?

No Read Or Write Between INSERT And UPDATE
I am using mysql with the InnoDB engine. I wrote a perl script that
first selects something from a table, and then updates a second table
based on the select from the first table. I need to make sure that
there is no read or write to the tables while my script performs the
insert and update.

I looked at

http://dev.mysql.com/doc/refman/4.1/en/lock-tables.html

and it says this:

---------------------------------------------------------
If you are using a storage engine in MySQL that does not support
transactions, you must use LOCK TABLES if you want to ensure that no
other thread comes between a SELECT and an UPDATE. The example shown
here requires LOCK TABLES to execute safely:

LOCK TABLES trans READ, customer WRITE;
SELECT SUM(value) FROM trans WHERE customer_id=some_id;
UPDATE customer
SET total_value=sum_from_previous_statement
WHERE customer_id=some_id;
UNLOCK TABLES;

Without LOCK TABLES, it is possible that another thread might insert a
new row in the trans table between execution of the SELECT and UPDATE
statements.
---------------------------------------------------------

However, I am using InnoDB and it DOES support transaction. So, does
that mean that even if I don't lock my tables, it will still work? If
not, what do I need to do?

Can Seqouia/c-jdbc Segregate Read & Write DBs ?
I haven't started using Seqouia/C-jdbc but I am probing the ability to
do something like this:

Controller CW has 2 controllers under it - C1 & C2.
C1 is local and has a host HA with X.db's tables t1 & t2.
C2 is remote and has a host HB also with X.db's tables t1 & t2.

Controller CR has 2 controllers under it - C3 & C4.
C3 is local and has a host HC with X.db's tables t1 & t2.
C4 is remote and has a host HD also with X.db's tables t1 & t2.

X.db is MySql type, from experience I've found that MySQL is better off
doing either only writes or only reads. So I want to segregate my read
and write stores. Hence, HA & HB are meant for writing into, while HC &
HD are meant for reading.

Now writes coming to CW should be treated like transactions which land
up on local and remote X.db. These are my WRITE-ONLY-DBs.

Likewise I want reads to goto CR for load balancing with higher
priority to read from HC than HD.

Questions:
(a) Can c-jdbc help in replication of data between HA-HC and HB-HD ?
Which component would help to set this up ? Or do I need to go for
out-of-band MySQL replication ?

(b) Is this configuration optimal for high scalability or availability
? Should I have added contollers horizontally instead of vertically ?

(c) Also, looks like I'd have to write an app to direct writes to CW
and read to CR. Or is there a way to obviate that with the controller
settings itself ?

Obtaining Read/Write Ratio -Replication
I am looking into a replication strategies to improve performance and
redundancy for a MySQL 4.1 database running on
Linux/Apache2/mod_python.

I am curious as to the standard way of obtaining a Read/Write Ratio.

What patterns of usage suit One-Way or Two-Way replication?

In my situation I have a database that intermittent bursts of inserts
through the day, a lot of batched deletes/inserts/updates over night
and fairly constant selects. I'd expect the RW Ratio would fluctuate
dramatically in a 24 hour period.

Replication Database On Slave Is Not Updated
We are using mysql v4.0.24 on Linux machines.

I'm trying to set up replication with a master and only one slave
database.

The syncronization seems to work well, the relay file on slave is wrote
correctly (Read_Master_Log_Pos and Relay_Log_Pos are updated) but the
information on slave database is not updated.

Master and slave are sincronyzed as shown below but for some reason I
don't understand. I have to say thay sometimes the replication worked but sometimes
setting up a new slave or changing a slave to master it does not. Code:

Master Master Replication, Truncate Table Fails, Version 5.0.22
I have set up a master master replication link between two databases, prototyping an idea. Anyhow, adding records and deleting works, both get updated, creating and dropping tables works, but truncating tables doesn't work.

My main questions are, why doesn't truncate work, and if it doesn't work, what else doesn't work across a master-master replication link?

Can't Create/write To Database And Temp Directories
I'm quite new to mySQL, and am having problems being able to create database tables. I'm on my [5th] setup of mySQL/apache/php, just after formatting windows, and everything else seems to be working, and working together, so I'm assuming the installation is ok. I can create databases, but when I try to create a table, I get this error message:

ERROR 1 (HY000): Can't create/write to file '.customermidian.frm' (Errcode: 2)

I'm assuming I haven't set up my temp folder correctly, but I don't understand how it works properly. After reading the mySQL maual repeatedly, I tried using

--tmp=c: emp

to set it, and it didn't seem to work, and I created a my.ini file with this in it:

[mysqld]
tmpdir=C:/temp

is there anything else I can try? I am logging into mySQL with the root account, and a password.

Read Or Convert 5.0 Database To 4.0 Or 3.0
using 5.0 - is there a way to attach remotely to 5.0 databases with a 4.0 or 3.0 client - or is there a way to convert 5.0 databases to 4.0 or 3.0 format?

Search And Write, Or Write And Recover?
The problem: I need to generate a 'unique string' for each row in a table. I already use auto_increment for system dependencies between tables.

What is the best approach one of these or another?

After generating a candidate 'unique string' the two strategies that came to mind are:

1. to then search the table's column to see if it is already assigned; locking the table for write while searching and writing the new row, or

2. set the column to UNIQUE when defining the table. Just go ahead and write the new row if you get a "non-unique" exception, generate another 'unique string' and try again.

I've tried both on a small XP laptop and get "lock timeout exceptions" rather quickly using #1. But replace those with lots of re-writes when there starts to get "collisions" of 'unique string's.

Master/Master Replication Problems
I was wondering whether someone could help me.

I have inherited set of replicated servers from a guy I used to work with, basic set up is:

Master1/Slave2 - Server ID 1
Master2/Slave1 - Server ID 2

When I looked at the logs it looks as though Server ID 2 is well out of step. Having looked at the slave status it appears that that Slave_SQL_Running state is set to No and a particular piece of SQL is causing the problem. Having ran the SQL on the particular server it has now problem at all.

Now my understanding of this type of set up is that Server ID 1 writes directly to Server ID 2, then Server ID 1 is in turn replicated to.

But for the life in me, I cannot understand why this piece of SQL is causing the problem. Can anyone offer me any ideas on how I can best get to the bottom of this, I'm really struggling with this. There was me thinking replication just worked!

Is there an ability to get MySQL to log all queries it is running, so I can get to the bottom of all queries currently being executed?

Master / Master Asynchronous Replication
Currently we have a web based application that is mostly reads (4:1
r/w). It is using a single MySQL database server. Is there any way to
have two database servers in a master/master configuration such that
writes to either database server are replicated to eachother. Basically
even though we have a 4:1 ration of read/write the writes happen often
enought that when the database goes down the app stops working. I know
how to get this working in Oracle (insert big laugh here) but Oracle is
cost prohibitive. Any pointers?

How Can I Set The Login To Read 2 Different Database Tables?
I need to enable my login function to read data from 2 different tables in my database, the discussion forum table and the customer table. Right now, visitors can only login as customers because the login function is setup to read from the customer table, and not both of the tables.

I had a few website developers do the site for me, so I have tried to learn some javascript tutorials; I have learned the basics, but I cannot seem to figure out how to do this. I think it has to do with an "or" function or "if, then" statement.

How can I set it up so that the username and password can be read from either of the tables?

The current setup is listed below:

How To Dump The Database And Read It Back In?
Our database just slowed down radically. Certain queries drive CPU
utilization through the roof. This happened once before, and the technican
simply dumped the data to disk and then played it back in. This seemed to
correct database corruption. After than, the database went back to normal
performance. Does anyone know how to do this?

MySQL Slave
We are having problems where at least once a day our MySQL slave server gets about 1700 seconds behind the master.Any ideas what is causing this? I looked in .err logs and it says nothing.A simple stop/start brings it back to to 0.

How To Write This In MySQL?
I Want to check if a certain value doesnt exist in a ceratain field in
my table as a condition.

"select id from employees inner join
translog on employees.emp_id=translog.empid
inner join outofofficedays on
employees.emp_id=outofofficedays.emp_id inner join holidays
where (here is my question: &#55614;&#57159;-04-02'
doesnt exist in the column translog.vtransdate)
and (&#55614;&#57159;-04-02' not between holidays.H_START and H_END)
order by translog.vTransDate,employees.emp_id limit 1"

Write A Mysql Db Onto A Cd
how to write the db of a mysql on one system onto a cd so as to copy that db to the mysql on another system.
The 2 systems are not connected to each other.

Vita And Write In MySQL
My web site use a odbc to a MySQL database. I create it on XP and it's worked but I'm now on vita and the data base on a 2003 server. All the reading access on the database are ok but all the writing access are denied. I haven't error message just error 500 on ie7.

Cannot Create Mysql-test/var/run/master.pid
ERROR: /tmp/edearl/mysql-4.0.21/mysql-test/var/run/master.pid was not
created in 30 seconds; Aborting

This error occurred just after MySQL was installed by root on Linux
2.4.22 (Fedora).

Dual Master Replication In MySQL 4.1
I have been charged with the task of setting up dual master replication with our company's MySQL servers. I know that their are inherent auto_increment problems with this set up, and the mechanisms to fix this problem weren't introduced until version 5.0.

Can anyone give me some tips, pointers, or advice for safely setting up this replication topology in 4.1? Are there any steps I can take to make sure there are no auto_increment issues? Has anyone had experience setting this up?

How Do I Write Multiple Conditions In An MySQL IF Statement?
I am using version 3.23 and want to write something like this:

Write/Move/Save Pdf To MySQL Table?
Is upload the only way to pass a file to a table?

I have a contract.pdf which is generated via php (FPDF) and saved in a temporary file. I need that file then moved /written to the appropriate customer table in our database (MySQL ) so that the temporary file may then be overwritten by the next Customer/Order.

While I am able to upload the file via <input type="file" name="uploadpdf> and related uploader.php, asking our web-users to “browse” for the file does not suit our needs, and of course adding a default value

<input type="file" name="uploadpdf" value="http://localhost/crm/modules/Orders/contract.pdf"> doesn’t work either.

As the file is always the same (modules/Orders/contract.pdf) how may I manage that move automatically, (say with a ‘Confirm Order’ button) to the database?

Read From MySQL
I installed appache server and php and work great..

But i cant read data from my sql .

I use this code to connect to connect to mySQL
server:localHost
username:root
password: ****

// Connect to the database server
$dbcnx = @mysql_connect('localhost', 'root', 'password');
if (!$dbcnx) {
die( '<p>Unable to connect to the ' .
'database server at this time.</p>' );
}
...
......
Should i create another account in mySQL? .or the php can not communicate with mySQL ?''

Can't Read Dir Of './mysql/'
I am currently using MySQL under Mandrake Linux 10.1 and have an error (see the title).
I have an error message when I want to see All the databases into my Server. I am using PHPMyAdmin.

Mysql Client Dont Write Russian Characters
I installed MySQL 5.0.24 and have so problem.I can't type russian characters in mysql client. Database has data in koi8 encoding (from dump, from php form...) and this data correctly shown.But when I switch encoding to russian in mysql client cursor stops moving independent from my keys pressing,when switch it return to English and typing continued.For example,I enter: select fld from tbl where fld="russ",if "russ" in koi8 I cann't type this characters.
It is possible type russian characters from OS command line: mysql -p -e "select fld from tbl where fld='russ'" dbname. In this case whole query text will be entered and correctly executed.
If anybody know how possibly decide this problem write me please.
My OS is ALTLINUX 3.0 : LANG=ru_RU.KOI8-R
MySQL's variables:
| character_set_client | koi8r
| character_set_connection | koi8r
| character_set_database | koi8r
| character_set_filesystem | binary
| character_set_results | koi8r
| character_set_server | koi8r
| character_set_system | utf8
| character_sets_dir | /usr/local/mysql-standard-5.0.24-linux-i686/share/mysql/charsets/ |
| collation_connection | koi8r_general_ci
| collation_database | koi8r_general_ci
| collation_server | koi8r_general_ci

MySql OUTFILE, ERROR 1 (HY000): Can't Create/write To File
I have problems in exporting tables to a file in MySql using OUTFILE option. MySql machine is a remote one. I got the error

ERROR 1 (HY000): Can't create/write to file '/tmp/scshekaran/bcp_table.txt' (Err 2)

How To Read A File Into Mysql
i have created a output file from oracle which contains insert statements like

insert into tms.tms_rem_exclusion_list values (3337)
insert into tms.tms_rem_exclusion_list values (4175)
insert into tms.tms_rem_exclusion_list values (4227)

iam new to mysql and someone told me that if i create such a file
i can import this data into the mysql table at the mysql prompt

like
mysql> tms_rem_exclusion_list < test.sql; (name of the above file)

but iam getting this:

ERROR 1064: You have an error in your SQL syntax. Check the manual that corresp
onds to your MySQL server version for the right syntax to use near 'tms_rem_excl
usion_list < test.sql' at line 1

first of all i am not sure where to keep the test.sql file ie in which directory
so i kept it under the scripts folder.

Read Mysql Dump File?
I am trying to read a mysql dump file that I was given from a mysql dump
file into my mysql server, I am using the following command at the mysql
command line and get the following error:

mysql> items < c:ookingsitems.mtsql
ERROR:
Unknown command ''.
ERROR:
Unknown command 'i'.

Does anyone know what this means?

Read MS SQLServer Data From MYSQL
what is the interface in MYSQL that I can use to read MS SQLServer data ?
Either programing in the code or tools are fine.

MySQL Query Browser. Result Set Read Only
I'm developing a client/server application with mySQL. The mysql server (plus database) is on a Windows XP LAN pc in another office.

I installed mySQL several months ago and have been using Administrator and Querybrowser locally on a regular basis to administer and fiddle with the database on the other pc. I don't believe I've changed any settings.

I noticed a couple of weeks ago that if I tried to edit table data from Administrator, Query browser wouldnt display but would remain in task manager process list forever. If I did the same directly from query browser I would receive a result set ready to edit.....

Read Excel Datas And Store In Mysql Table
In a button click, i have to read datas from excel sheet and store it in mysql database table. Code:

MySQL 4.1 & JDBC 3 Cannot Save & Read Asia (Big) Character
MySQL 4.1 can save & read character through JDBC 3.
Is any trick to save & read Asia (Big5) Chinese character ?

MySQL Query To Read Field, Split And Rewrite Fields
I need help reading a field from a table, splitting the contents of the field and rewriting the contents to two other fields. ....

NOW() At Replication Slave
We have 3 MySQL servers in replication, 1 master and 2 slaves. One slave server is 300 seconds behind the master due to heavy SELECT statements.

If I run INSERT query (containing NOW()) on master at 10:00 AM, what time will be saved in the slave server which is 300 seconds behind? The query is

MySQL Code:

INSERT INTO users (username, password, signup_date_time) VALUES ('user1', 'pass1', NOW())

Can't Set-up Slave In Replication
I'm trying to set-up replication following a book and on-line guide. This
is over SSH with a tunnel (not sure if the tunnel's required).

The master appears to be set-up OK (easy enough).
The slave however isn't behaving.

I've got a my.cnf with the following:

[mysqld]
master-host=127.0.0.1
master-port=APORT
master-user=AUSER
master-password=APASSWORD
server-id=2

When I start the slave up and do show slave status - I get 0's and
blank entries back.

I've tried setting it up using:

CHANGE MASTER TO
MASTER_HOST='localhost',
MASTER_USER='AUSER',
MASTER_PORT=APORT,
MASTER_PASSWORD='APASSWORD',
MASTER_LOG_FILE='AFILE-bin.001',
MASTER_CONNECT_RETRY=10;

This sets up the slave bits and show slave status looks much better,
however,

SLAVE START;

returns...

The server is not configured as slave, fix in config file or with CHANGE
MASTER TO

I can only seem to find this message in pages that have a list of all Mysql
errors.